set analysis issue in pivot table

[kkkumar82]

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.

[tresesco]

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

[avinashelite]

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

[kkkumar82]

I have attached a qvw file for references

[avinashelite]

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.

[kkkumar82]

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.

[kkkumar82 · Accepted solution]

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]))