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.
