Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I created a pivot table with four dimensions and 2 Kpi's.
i can see the correct values, when i expand all the dimensions at one time.
if i expand dimension by dimension wise, i am not able to see the correct kpi values.
Please find the attached files and can any one help me on this issue.
Its working fine.
=if(((v1) - (If(Dimensionality() = 3, Aggr(Rank(Total sum(TotalPaid)/v1), ServDate,MealPeriod,[Time Slot]), Rank(Total sum(TotalPaid)/v1))))/(v1)<0.1,'0-10%',
if(((v1) - (If(Dimensionality() = 3, Aggr(Rank(Total sum(TotalPaid)/v1), ServDate,MealPeriod,[Time Slot]), Rank(Total sum(TotalPaid)/v1)))) /(v1)<0.2,'10-20%',
if(((v1) - (If(Dimensionality() = 3, Aggr(Rank(Total sum(TotalPaid)/v1), ServDate,MealPeriod,[Time Slot]), Rank(Total sum(TotalPaid)/v1)))) /(v1)<0.3,'20-30%',
if(((v1) - (If(Dimensionality() = 3, Aggr(Rank(Total sum(TotalPaid)/v1), ServDate,MealPeriod,[Time Slot]), Rank(Total sum(TotalPaid)/v1)))) /(v1)<0.4,'30-40%',
if(((v1) - (If(Dimensionality() = 3, Aggr(Rank(Total sum(TotalPaid)/v1), ServDate,MealPeriod,[Time Slot]), Rank(Total sum(TotalPaid)/v1)))) /(v1)<0.5,'40-50%',
if(((v1) - (If(Dimensionality() = 3, Aggr(Rank(Total sum(TotalPaid)/v1), ServDate,MealPeriod,[Time Slot]), Rank(Total sum(TotalPaid)/v1)))) /(v1)<0.6,'50-60%',
if(((v1) - (If(Dimensionality() = 3, Aggr(Rank(Total sum(TotalPaid)/v1), ServDate,MealPeriod,[Time Slot]), Rank(Total sum(TotalPaid)/v1)))) /(v1)<0.7,'60-70%',
if(((v1) - (If(Dimensionality() = 3, Aggr(Rank(Total sum(TotalPaid)/v1), ServDate,MealPeriod,[Time Slot]), Rank(Total sum(TotalPaid)/v1)))) /(v1)<0.8,'70-80%',
if(((v1) - (If(Dimensionality() = 3, Aggr(Rank(Total sum(TotalPaid)/v1), ServDate,MealPeriod,[Time Slot]), Rank(Total sum(TotalPaid)/v1)))) /(v1)<0.9,'80-90%','90-100%')))))))))
=
if(((count(total aggr( sum(TotalPaid),ServDate,MealPeriod,[Time Slot]))) - (If(Dimensionality() = 3, Aggr(Rank(Total sum(TotalPaid)/v1), ServDate,MealPeriod,[Time Slot]), Rank(Total sum(TotalPaid)/v1))))
/
(count(total aggr( sum(TotalPaid),ServDate,MealPeriod,[Time Slot])))<0.1,'0-10%',
if(((count(total aggr( sum(TotalPaid),ServDate,MealPeriod,[Time Slot]))) - (If(Dimensionality() = 3, Aggr(Rank(Total sum(TotalPaid)/v1), ServDate,MealPeriod,[Time Slot]), Rank(Total sum(TotalPaid)/v1))))
/
(count(total aggr( sum(TotalPaid),ServDate,MealPeriod,[Time Slot])))<0.2,'10-20%',
if(((count(total aggr( sum(TotalPaid),ServDate,MealPeriod,[Time Slot]))) - (If(Dimensionality() = 3, Aggr(Rank(Total sum(TotalPaid)/v1), ServDate,MealPeriod,[Time Slot]), Rank(Total sum(TotalPaid)/v1))))
/
(count(total aggr( sum(TotalPaid),ServDate,MealPeriod,[Time Slot])))<0.3,'20-30%',
if(((count(total aggr( sum(TotalPaid),ServDate,MealPeriod,[Time Slot]))) - (If(Dimensionality() = 3, Aggr(Rank(Total sum(TotalPaid)/v1), ServDate,MealPeriod,[Time Slot]), Rank(Total sum(TotalPaid)/v1))))
/
(count(total aggr( sum(TotalPaid),ServDate,MealPeriod,[Time Slot])))<0.4,'30-40%',
if(((count(total aggr( sum(TotalPaid),ServDate,MealPeriod,[Time Slot]))) - (If(Dimensionality() = 3, Aggr(Rank(Total sum(TotalPaid)/v1), ServDate,MealPeriod,[Time Slot]), Rank(Total sum(TotalPaid)/v1))))
/
(count(total aggr( sum(TotalPaid),ServDate,MealPeriod,[Time Slot])))<0.5,'40-50%',
if(((count(total aggr( sum(TotalPaid),ServDate,MealPeriod,[Time Slot]))) - (If(Dimensionality() = 3, Aggr(Rank(Total sum(TotalPaid)/v1), ServDate,MealPeriod,[Time Slot]), Rank(Total sum(TotalPaid)/v1))))
/
(count(total aggr( sum(TotalPaid),ServDate,MealPeriod,[Time Slot])))<0.6,'50-60%',
if(((count(total aggr( sum(TotalPaid),ServDate,MealPeriod,[Time Slot]))) - (If(Dimensionality() = 3, Aggr(Rank(Total sum(TotalPaid)/v1), ServDate,MealPeriod,[Time Slot]), Rank(Total sum(TotalPaid)/v1))))
/
(count(total aggr( sum(TotalPaid),ServDate,MealPeriod,[Time Slot])))<0.7,'60-70%',
if(((count(total aggr( sum(TotalPaid),ServDate,MealPeriod,[Time Slot]))) - (If(Dimensionality() = 3, Aggr(Rank(Total sum(TotalPaid)/v1), ServDate,MealPeriod,[Time Slot]), Rank(Total sum(TotalPaid)/v1))))
/
(count(total aggr( sum(TotalPaid),ServDate,MealPeriod,[Time Slot])))<0.8,'70-80%',
if(((count(total aggr( sum(TotalPaid),ServDate,MealPeriod,[Time Slot]))) - (If(Dimensionality() = 3, Aggr(Rank(Total sum(TotalPaid)/v1), ServDate,MealPeriod,[Time Slot]), Rank(Total sum(TotalPaid)/v1))))
/
(count(total aggr( sum(TotalPaid),ServDate,MealPeriod,[Time Slot])))<0.9,'80-90%','90-100%')))))))))