Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi ,
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.
can any one help me.
May be this?
=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%')))))))))
Can you send some screenshots that depicts your issue and expected output?
The below screen shot is, when i expand pivot table at a time.
2)when i expand the pivot table by each dimension wise.Spend is coming correctly but %Spend is coming wrong.
What is the expression for %Spend in this table ?
=if(((count(total aggr( sum(TotalPaid),ServDate,MealPeriod,[Time Slot]))) - (Rank(Total sum(TotalPaid)/v1)))
/
(count(total aggr( sum(TotalPaid),ServDate,MealPeriod,[Time Slot])))<0.1,'0-10%',
Repeating the formula for all %s upto 90-100%, v1 is an Variable.
Can you share a sample to look into?
How to add the sample app, i am not able to add here.
Hi ,
Please find the attached one.
Thats strange, when i expand all it give me correct %'s but when i expand individually it takes the last value 90-100%.
And as you are already getting the Total count for your time in your variable v1, for your Spend% why dont you use this?
=if(((v1) - (Rank(Total sum(TotalPaid)/v1))) / (v1)<0.1,'0-10%',
if(((v1) - (Rank(Total sum(TotalPaid)/v1))) / (v1)<0.2,'10-20%',
if(((v1) - (Rank(Total sum(TotalPaid)/v1))) / (v1)<0.3,'20-30%',
if(((v1) - (Rank(Total sum(TotalPaid)/v1))) / (v1)<0.4,'30-40%',
if(((v1) - (Rank(Total sum(TotalPaid)/v1))) / (v1)<0.5,'40-50%',
if(((v1) - (Rank(Total sum(TotalPaid)/v1))) / (v1)<0.6,'50-60%',
if(((v1) - (Rank(Total sum(TotalPaid)/v1))) / (v1)<0.7,'60-70%',
if(((v1) - (Rank(Total sum(TotalPaid)/v1))) / (v1)<0.8,'70-80%',
if(((v1) - (Rank(Total sum(TotalPaid)/v1))) / (v1)<0.9,'80-90%','90-100%')))))))))