Pivot Calculation Problem

[Anil_Babu_Samineni]

Dear Friends,

Why this pivot table getting problem

Please check the calculation? Sum. Left of everything is working but this is not working

Correct Value is - 30, But here showing 29.

Can i know what is modification i required

[PrashantSangle]

Hi,

try with aggr()

sum(Aggr(YourExpression,dim1,dim2))

like for Week

sum(Aggr(sum({<C_DESCRIPTION={'Reportable','Recordable','Restricted work Cases','Lost Work Cases'} >} S_COUNT),C_DESCRIPTION,dim2))

Regards

[Anil_Babu_Samineni]

My second dimension is Calculated dimension, =if(wildmatch(P_Des,'All Plants')=0,P_Des)

If i apply your expression, Nothing 0 Values showing

And i want to ask, Can i know how to calculate YTD?

I am using this for YTD

Sum({<[Master Year]=, [Master Month]=, Quarter=, [Master Week]=, DATE=, Desc={'A','B','C','D'}, DATE={">=$(=Num(YearStart(Max(DATE))))<=$(=Max(DATE))"}>} DISTINCT Sales)

[PrashantSangle]

Hi,

What you tried???

try below

sum(Aggr(sum({<C_DESCRIPTION={'Reportable','Recordable','Restricted work Cases','Lost Work Cases'} >} S_COUNT),C_DESCRIPTION,P_Des))

or why you required calculated dimension

just add P_Des and expression could be

sum({<C_DESCRIPTION={'Reportable','Recordable','Restricted work Cases','Lost Work Cases'},P_Des-={"All Plants"} >} S_COUNT)

Regards,

[Anil_Babu_Samineni]

@Max, time god it's working.

But, i want to implement YTD Calculation also?

[PrashantSangle · Accepted solution]

Hi,

do same as whichever solution work?? which work aggr() or adding P_Des-={"All Plants"} in set analysis?

try below

using aggr()

=sum(aggr(sum({<[Master Month]={">=1 <=$(vMaxMonth)"},C_DESCRIPTION={'Reportable','Recordable','Restricted work Cases','Lost Work Cases'}>} S_COUNT),C_DESCRIPTION,P_Des))

or

sum({<[Master Month]={">=1 <=$(vMaxMonth)"},C_DESCRIPTION={'Reportable','Recordable','Restricted work Cases','Lost Work Cases'},P_Des-={"All Plants"} >} S_COUNT)

I will suggest remove calculated dimension and update set analysis, because calculated dimension is performance killer.

Regards,

[Anil_Babu_Samineni]

Max, How to test manually is that YTD values are correct or Not?

[PrashantSangle]

Test in straight table

take date/ month field add expression which you use to calculate YTD value.

Check you get proper value in front of those dates...

also don't give name to expression label. At front end put your mouse pointer on it and check what it is evaluating.

Regards

[Anil_Babu_Samineni]

Max, Nothing diff there. Because, we just copy that Expression and apply same thing

Can i know how this value coming?

Explain me please?

[PrashantSangle]

Hi,

that why I tell you to take date dimension. It will give you idea that those dates are within YTD or not.

also In chart when you take your pointer on expression label it will highlight date period which you are comparing.

In your expression it will generate expression for [Master Month]={">=1 <=$(vMaxMonth)"}

Regards,

[Anil_Babu_Samineni]

Max, Yes i remove that calc dimension and choose normal thing. But Exclude is not working while i am using that

In that expression, What is the happen?

As i understand, here in b/w of 1 and 12 Months data fetching to that field.

So, Can you please explain me how many fields are acting for tSum(his YTD calculation

1) C_DESCRIPTION

2) Sum(S_COUNT) where month in b/w of 1 and 12 (Jan --- Dec)

3) That expression need to show the C_DESCRIPTION values

4) P_DES , Here those two dim are Depends

What is the form of aggr here.