Skip to main content

Qlik

cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Not applicable
‎2017-02-17 11:49 AM

Issue in Pivot Table

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.

Sample2.qvw
116 KB
131 KB
461 Views
0 Likes
2 Replies
Not applicable
‎2017-02-17 01:04 PM
Author

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%')))))))))

382 Views
Anonymous
Not applicable
‎2017-02-17 01:39 PM
Author

=

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%')))))))))

382 Views