Retaining Equation with Pivot Table

Anonymous — Thu, 18 Feb 2016 14:13:20 GMT

I have an expression that I am trying to apply in a pivot table for the attached QlikView document.

There is one Base Scenario and 4 alternative Scenarios.

The expression is applied at a Regional level, and takes the minimum of RangeMin((Base – Scenario1), (Base – Scenario2), (Base – Scenario3), (Base – Scenario4)) for each Region.

However, both Canada and the US need to choose the same Scenario (i.e., for North America). For example:

RangeMin(

(BaseUS + Base Canada) – (Scenario1Canada + Scenario1US) ,

(BaseUS + Base Canada) – (Scenario2Canada + Scenario2US) ,

(BaseUS + Base Canada) – (Scenario3Canada + Scenario3US) ,

(BaseUS + Base Canada) – (Scenario4Canada + Scenario4US)

How can I achieve this so that even if a user drills down, they can still see the correct results (i.e., Scenario 1)? If I try to aggregate the function by Jurisdiction, as I did in the second table, then the total is allocated only to Canada.

If I aggregate the function by Region, then it does not provide the correct output when a user expands to see Region (i.e., Canada uses Scenario 2 and the US uses Scenario 1).

I am really stuck on this, and would appreciate some help.

Re: Retaining Equation with Pivot Table

Gysbert_Wassenaar — Sat, 20 Feb 2016 17:19:19 GMT

If I try to aggregate the function by Jurisdiction, as I did in the second table, then the total is allocated only to Canada.

This will solve that issue:

=sum(aggr(nodistinct RangeMin($(InterestRateScenario1CF),$(InterestRateScenario2CF),$(InterestRateScenario3CF),$(InterestRateScenario4CF)), Jurisdiction))

Does that solve your problem?

topic Retaining Equation with Pivot Table in QlikView

Retaining Equation with Pivot Table

Re: Retaining Equation with Pivot Table