topic Re: set analysis issue in pivot table in QlikView

set analysis issue in pivot table

kkkumar82 — Tue, 10 Feb 2015 05:46:17 GMT

In the above table max and min are the fields containing the maximum and minimum savings percentage per CM/ODM

and used the following expression

Sum({<QuarterYear={$(vCurrentQuarter), $(vLastQuarter1), $(vLastQuarter2), $(vLastQuarter3)}>}[CPN Savings])

Sum({<QuarterYear={$(vCurrentQuarter), $(vLastQuarter1), $(vLastQuarter2), $(vLastQuarter3)}>}Spend)

Requirement:

Actually I want to have a variable where it is the difference of maxiumum and minimum percentages, let us say vCM/ODM

I created a variable by using max(above expression) - min(above expression) and it is giving me correct value 25% in a text box,

my concern is I want to use this variable in the pivot table for savingsopportunity field as savings$* $(vCM/ODM),

what I found is the variable value became 0%.

I think the variable formula is taking dimension into consideration . how to get minimum and maximum percentages in the variable even if I use it in pivot table.

Re: set analysis issue in pivot table

tresB — Tue, 10 Feb 2015 05:55:05 GMT

While defining the variable, put '=' sign at the beginning and try.

Re: set analysis issue in pivot table

avinashelite — Tue, 10 Feb 2015 06:34:17 GMT

Hi Kiran,

As per my knowledge the variables wont act according to the dimension, the issue is with some thing else.

Please post your app, lets have a check

Re: set analysis issue in pivot table

kkkumar82 — Tue, 10 Feb 2015 06:55:23 GMT

I have attached a qvw file for references

Re: set analysis issue in pivot table

avinashelite — Tue, 10 Feb 2015 07:11:35 GMT

Hi Kiran,

The issue is not with the variable what you have created, the issue is with the number format which you set in the number tab.

Please find the attachment for the solution.

Re: set analysis issue in pivot table

kkkumar82 — Tue, 10 Feb 2015 07:29:02 GMT

Hi,

I got that but what I want is I want difference of maximum and minimum savings percentage ie 0.72 - 0.57 = 0.25

but have u observed that the seavings opportunity is showing different values when we take it in the table,

when we are taking it in text box it is showing correct value , if we can get it , I have to calculate the actual opportunity ie savings * variable.

or can u try this

How to get 0.72% in max and 0.47% in min columns for all CM/ODMs. because u can see combo graphs in the qvw to find the min and max %s.

Re: set analysis issue in pivot table

kkkumar82 — Tue, 10 Feb 2015 09:12:36 GMT

Hi all,

Finally got how to prevent Dimension effect on the expression, created the variable with the following formulas subtraction.

Just need to add TOTAL qualifier(after knowing every thing is easy....)

max(TOTAL aggr(Sum({<QuarterYear={$(vCurrentQuarter), $(vLastQuarter1), $(vLastQuarter2), $(vLastQuarter3)}>}[CPN Savings])

Sum({<QuarterYear={$(vCurrentQuarter), $(vLastQuarter1), $(vLastQuarter2), $(vLastQuarter3)}>}Spend),[CM/ODM]))

Min:

min(TOTAL aggr(Sum({<QuarterYear={$(vCurrentQuarter), $(vLastQuarter1), $(vLastQuarter2), $(vLastQuarter3)}>}[CPN Savings])

Sum({<QuarterYear={$(vCurrentQuarter), $(vLastQuarter1), $(vLastQuarter2), $(vLastQuarter3)}>}Spend),[CM/ODM]))