Problem in automatical sum of pivot table

[Vince_CH]

 Hello Community,

I make a pivot tabel as below to list the YTD budget in conditional expressions as following, assignment of different yearly numbers as per different conditions. the result is, that the figures in pivot table cannot be summed automatically.  how shall i resovle this issue? or any other alternative ways? thanks

 

Num(
(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 [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 [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 [Order Type]='Y400','650',
If(Order_Material_Pricing_Group_Text='repair works' and [Order Type]='Y400','0'))))))),

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

[Vince_CH]

I tried as per you suggested like following expressions in Kpi:
Num((If(num(daynumberofyear(Max(Date)-1),'##,###')>181,num(daynumberofyear(Max(Date)-1),'##,###')-181,num(daynumberofyear(Max(Date)-1),'##,###')+181)/365)*
Sum({<Order_Material_Pricing_Group_Text = {'spare parts'}, Subsidiary = {'Subsidiary'}, [Order Type] = {'Y410'}>} 2400), '¥#,##0K;($#,##0.00)'), the result came out is still 1006K, different from 999K from the pivot table.

[Vince_CH]

@Anil_Babu_Samineni:
I verified the data level, and the result of 1006K is correct. The point differentiate here is because of Max(date) in the following expression to calculate percentage of duration of max(date) out of the year.
If(num(daynumberofyear(Max(Date)-1),'##,###')>181,num(daynumberofyear(Max(Date)-1),'##,###')-181,num(daynumberofyear(Max(Date)-1),'##,###')+181)/365

In KPI, since there is no restriction, so the Max(date) pick up the real max date from the database, while in pivot table, it only extracted the 'max' date under certain restriction as described. However from here, the former real max date of whole database is expected. So how shall I correct the expressions in the pivot table to get the Max(date) anyway regardless of thoese restrictions?

[Anil_Babu_Samineni]

May be use like

Sum({<Order_Material_Pricing_Group_Text = {'spare parts'}, Subsidiary = {'Subsidiary'}, [Order Type] = {'Y410'}, Date={$(=Max(Date))}>} 2400)

[Vince_CH]

Hello, do you mean this for Kpi or for pivot table? there might be some misunderstanding.
The results out of the KPI is corrects as expected, instead I expect the budget results from the pivot table could be the same as 1006K.
In the expression of pivot table, there are two parts are multiplied, like following, the second part of the sum(aggr()) works now great, just the first part to calculate the percentage of max(date) duration is problematical, so how to just extract the absolute max(date) in daynumberofyear() from the database??

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

[Anil_Babu_Samineni]

Would you able to share a sample application?
Curious to know what is return in pivot table?
If(num(daynumberofyear(Max(Date)-1),'##,###')>181,num(daynumberofyear(Max(Date)-1),'##,###')-181) ??

[Vince_CH]

@Anil_Babu_Samineni:

As you might know, we are not able to upload the sample qlik file into the community.
Having verified this on data level, in KPI, it picks up the max date as per whole database, say, 2018/11/30, while in piviot table, due to the constraints of conditional dimensions, it returned the max(date) some date earlier than 2018/11/30, e.g. 2018/11/25.

I just tried to set up some variable like following, then use it to multiple the aggr() function with conditional expressions, but it returns zero for all rows.
Let vMaxdate=If(num(daynumberofyear(Max(Date)-1),'##,###')>181,num(daynumberofyear(Max(Date)-1),'##,###')-181,num(daynumberofyear(Max(Date)-1),'##,###')+181)/365

or we we have to extract the absolute max(date) in above expressions? but how?

[Anil_Babu_Samineni]

You may restrict like
Num((If(num(daynumberofyear(Max({1}Date)-1),'##,###')>181,num(daynumberofyear(Max({1}Date)-1),'##,###')-181,num(daynumberofyear(Max({1}Date)-1),'##,###')+181)/365)*Sum(Aggr(....)),'#,##0K')

[Vince_CH]

I tried, but the results kept same. also put Max({1}Date) element into daynumberof year as variable try with return zero too.

[Anil_Babu_Samineni]

Can you post full expression?
And, check this max({1} date) in kpi and tell me what it returns?

[Vince_CH]

Hello, I didn't get your intention here. As said, in KPI, it has been verified manually that works fine with 1006K as expected. The point here is regarding how to get the abosoluate max(date) in the pivot table.