To get the absolute max(date) of database in pivot table

[Vince_CH]

Hello Community,

I have an issue like picture illustrate as following, the same item with YTD budget in KPI and in pivot table are different in outcome.  

In KPI, the expressions are as below:

Num(
(If(num(daynumberofyear(Max(Date)-1),'##,###')>181,num(daynumberofyear(Max(Date)-1),'##,###')-181,num(daynumberofyear(Max(Date)-1),'##,###')+181)/365)*2400,
'¥#,##0K;($#,##0.00)')

In pivot table, the expressions are as following:

Num(
Sum(Aggr((If(num(daynumberofyear(Max(Date)-1),'##,###')>181,num(daynumberofyear(Max(Date)-1),'##,###')-181,num(daynumberofyear(Max(Date)-1),'##,###')+181)/365)*

If(Dim1='spare parts' and Dim2='Subsidiary' and Dim3='Y410','2400',
If(Dim1='spare parts' and Dim3='Y410','14550',
If(Dim1='grinding media' and Dim2='Subsidiary' and Dim3='Y410','0',
If(Dim1='grinding media' and Dim3='Y410','2000',
If(Dim1='service' and Dim2='Subsidiary' and Dim3='Y400','0',
If(Dim1='service' and Dim3='Y400','650',
If(Dim1='repair works' and Dim3='Y400','0'))))))), Dim1, Dim2, Dim3)), '¥#,##0K;($#,##0.00)')

Having verified manually the difference, in KPI, the max(date) pick up the max date from the whole database, say, 2018/11/30. while in pivot table, due to the limitation of dimensions, the max(date) picked up is earier, say, 2018/11/25, which leads to the difference.

the question is,  is it possible to extract also the absolute max(date) of whole database in pivot table? or any other alternative suggestions? thanks!

[Gysbert_Wassenaar]

Yes, include that entire expression in another Aggr with the dimensions of the pivot table as the extra arguments for the Aggr function. And then put the Max function around all that.
Max(Aggr( num(Sum...etc), OrderType,Region,Category))

[Vince_CH]

@Gysbert_Wassenaar

, thanks for reply, i tried, but it didn't work out with the buget outcome kept same in pivot table. 

just to note that in the existing budget expression of pivot table, it contains two parts in the used aggr():

1. first part as below bold fonts is the formula to calculate the percentage of so far days of the year in the whole yearly days.

2. the second parts, that is , rest part is to assign the yearly fixed budget numbers to different IF conditional items. 

Currently, with dimensions in pivot table, the budget expression calcualtion results only return the max(date) under dimensional restriction. e.g in pivot table, the returned max(date) is 2018/11/25, while the max(date) of whole database is 2018/11/30 instead.

So my question is how to get the absolute max(date) from the whole database in part 1 as above?

Hope above explaination make sense now. thanks for your time. 

Num(Sum(Aggr(
(If(num(daynumberofyear(Max(Date)-1),'##,###')>181,num(daynumberofyear(Max(Date)-1),'##,###')-181,num(daynumberofyear(Max(Date)-1),'##,###')+181)/365)*

If(Order_Material_Pricing_Group_Text='spare parts' and Subsidiary='Subsidiary' and [Order Type]='Y410','2400',
If(Order_Material_Pricing_Group_Text='spare parts' and Subsidiary<>'Subsidiary'and [Order Type]='Y410','14550',
If(Order_Material_Pricing_Group_Text='grinding media' and Subsidiary='Subsidiary' and [Order Type]='Y410','0',
If(Order_Material_Pricing_Group_Text='grinding media' and Subsidiary<>'Subsidiary' and [Order Type]='Y410','2000',
If(Order_Material_Pricing_Group_Text='service' and Subsidiary='Subsidiary' and [Order Type]='Y400','0',
If(Order_Material_Pricing_Group_Text='service' and Subsidiary<>'Subsidiary' and [Order Type]='Y400','650',
If(Order_Material_Pricing_Group_Text='repair works' and [Order Type]='Y400','0'))))))),Order_Material_Pricing_Group_Text,Subsidiary,[Order Type])),

'¥#,##0K;($#,##0.00)')

[Vince_CH · Accepted solution]

Hello, all, as summarized for this case, Max(Date) has to be changed into Max(Total Date), then it will disregard the affect of the dimension, which works fine now.
Sum(Aggr((If(num(daynumberofyear(Max(Date)-1),'##,###')>181,num(daynumberofyear(Max(Date)-1),'##,###')-181,num(daynumberofyear(Max(Date)-1),'##,###')+181)/365)