topic Pivot table issue in QlikView

Pivot table issue

Anonymous — Thu, 16 Feb 2017 21:11:52 GMT

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.

Re: Pivot table issue

vishsaggi — Thu, 16 Feb 2017 21:31:48 GMT

Can you send some screenshots that depicts your issue and expected output?

Re: Pivot table issue

Anonymous — Thu, 16 Feb 2017 21:54:17 GMT

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.

Re: Pivot table issue

vishsaggi — Thu, 16 Feb 2017 22:04:55 GMT

What is the expression for %Spend in this table ?

Re: Pivot table issue

Anonymous — Thu, 16 Feb 2017 22:09:57 GMT

=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.

Re: Pivot table issue

vishsaggi — Thu, 16 Feb 2017 22:16:11 GMT

Can you share a sample to look into?

Re: Pivot table issue

Anonymous — Thu, 16 Feb 2017 22:34:29 GMT

How to add the sample app, i am not able to add here.

Re: Pivot table issue

Anonymous — Fri, 17 Feb 2017 15:20:53 GMT

Hi ,

Please find the attached one.

Re: Pivot table issue

Anonymous — Fri, 17 Feb 2017 16:35:47 GMT

shubham.singh‌

Re: Pivot table issue

vishsaggi — Fri, 17 Feb 2017 17:18:56 GMT

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

Re: Pivot table issue

vishsaggi — Fri, 17 Feb 2017 17:20:29 GMT

Will have to look into it. Will get back in a while. However mentioning Sunny if he has any quick resolution for this, i vaguely remember he did fix something like this long ago.

Hey stalwar1‌ would you mind looking into this please.

Re: Pivot table issue

sunny_talwar — Fri, 17 Feb 2017 17:31:54 GMT

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

(count(total aggr( sum(TotalPaid),ServDate,MealPeriod,[Time Slot])))<0.3,'20-30%',

(count(total aggr( sum(TotalPaid),ServDate,MealPeriod,[Time Slot])))<0.4,'30-40%',

(count(total aggr( sum(TotalPaid),ServDate,MealPeriod,[Time Slot])))<0.5,'40-50%',

(count(total aggr( sum(TotalPaid),ServDate,MealPeriod,[Time Slot])))<0.6,'50-60%',

(count(total aggr( sum(TotalPaid),ServDate,MealPeriod,[Time Slot])))<0.7,'60-70%',

(count(total aggr( sum(TotalPaid),ServDate,MealPeriod,[Time Slot])))<0.8,'70-80%',

(count(total aggr( sum(TotalPaid),ServDate,MealPeriod,[Time Slot])))<0.9,'80-90%','90-100%')))))))))

Re: Pivot table issue

vishsaggi — Fri, 17 Feb 2017 17:39:42 GMT

Thanks Sunny. That is awesome.

Ravi, as mentioned before anyways you are getting your count totals from your variable v1. You can just use this in your spend % to make it more comprehensive. Just replaced that count total with variable v1.

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

Re: Pivot table issue

Anonymous — Fri, 17 Feb 2017 17:50:47 GMT

Thanks a lot Sunny and Viswanath.

Its working fine.

Re: Pivot table issue

sunny_talwar — Fri, 17 Feb 2017 17:54:46 GMT

Glad we were able to help. Please close the thread by marking the correct response

Qlik Community Tip: Marking Replies as Correct or Helpful

Best,

Sunny

Re: Pivot table issue

sunny_talwar — Fri, 17 Feb 2017 17:55:41 GMT

And his name is Vishwarath

Re: Pivot table issue

Anonymous — Fri, 17 Feb 2017 20:04:05 GMT

Hi Sunny,

By Default the first dimension is showing 90-100%, is it possible to restrict this?it suppose to show calendar date wise.While expanding , it is working fine with other dimensions.

Re: Pivot table issue

sunny_talwar — Fri, 17 Feb 2017 20:05:30 GMT

Not sure I understand your question? What do you want to see instead of 90-100%?

Re: Pivot table issue

vishsaggi — Fri, 17 Feb 2017 20:09:05 GMT

Your spend is calculated based on the IF condition for Total spend and value your variable holds with a range.

Re: Pivot table issue

Anonymous — Fri, 17 Feb 2017 20:16:24 GMT

Every calendar date has its own %Spend Value, but here it is same for all the dates.