Forum Discussion
GetPivotData and Time Values as Cell References
Hello,
I seriously think that this is a bug. Actually maybe more than one bug.
First;
Using regular data model, if the source data includes a time value such as 00:00, you can't display those fields as times. 00:00 appears as 00 and changing number format doesn't do anything. In addition to this, you can't make a cell reference to use getpivotdata using a time value, let's says my cell a3 is 00:00 and I want to get that time's value but I can't reference it using a3. you have to hard code it as "0" and this kills the purpose because you can't flashfill this way.
Second;
Using Excel's Data Model,
You can get the pivot to display the time values correctly such as 00:00. However, as you know using getpivotdata with Excel's Data Model, things get complicated and again you can't reference a cell as a time value because excel reads the time as "1899-12-30T00:00:00" and referenced cell doesn't match this value.
Are these really bugs? If so how can I report them? Is there anyway around this?
I'd avoid using of implicit measures and GETPIVOTDATA(). For such sample
we may create explicit measure
Values sum:=SUM( [Value] )and return related value by
=CUBEVALUE( "ThisWorkbookDataModel", CUBEMEMBER( "ThisWorkbookDataModel", "[Table1].[Time].[" & TEXT( $K$4, "hh:mm" ) & "]"), CUBEMEMBER("ThisWorkbookDataModel", "[Measures].[Values sum]") )In the sample above time is selected from drop down list, please see attached.
7 Replies
I'd avoid using of implicit measures and GETPIVOTDATA(). For such sample
we may create explicit measure
Values sum:=SUM( [Value] )and return related value by
=CUBEVALUE( "ThisWorkbookDataModel", CUBEMEMBER( "ThisWorkbookDataModel", "[Table1].[Time].[" & TEXT( $K$4, "hh:mm" ) & "]"), CUBEMEMBER("ThisWorkbookDataModel", "[Measures].[Values sum]") )In the sample above time is selected from drop down list, please see attached.
Hmm I just learnt cube functions from your post and doing research on them. Can you use them without power pivot?
=CUBEVALUE( "ThisWorkbookDataModel",
CUBEMEMBER( "ThisWorkbookDataModel", "[PivotTable4].[Hours].["& TEXT(A3, "hh:mm" ) & "]"),
CUBEMEMBER("ThisWorkbookDataModel", "[Measures].[SLA]") )This formula returns #N/A the pivot's name is PivotTable4 the field to look for is Hours and the value is SLA. The pivot is in another sheet. What am i doing wrong?
In general yes, Power Pivot is only interface to data model. In this case to use implicit measures or create explicit measures by right click on table name
In attached file same formula with implicit measure, it works.
