Qlikview: Aggregation 2: Dynamic Aggregation in Pivot Table

Scenario:
We have a sales data about how many items each sales person sold in each month.
And we need a pivot table which:

• will show the number of sales person who sold more than two items in a given month.
• will show the number of sales person who sold more than five items in a given quarter (i.e. when the pivot table is collapsed to quarter level)
• will show the number of sales person who sold more than ten items in a given year (i.e. when the pivot table is collapsed to year level).

Solution:

• We will make the calculation with AGGR function but we need 3 different formulas (one for each different level, i.e. month, quarter, year). I prefer to keep the formulas in variables:
• vMthLevel = sum(if(aggr(sum(Quantity), Yr,Qtr,Mth,SalesPerson)>2,1,0))
• vQtrLevel = sum(if(aggr(sum(Quantity), Yr,Qtr,SalesPerson)>5,1,0))
• vYrLevel = sum(if(aggr(sum(Quantity), Yr,SalesPerson)>10,1,0))

• To find out at which level we are in the pivot table, we will use DIMENSIONALITY function

• And finally, in our expression, we will use PICK function to pick one of the three formulas according to DIMENSIONALITY.    
• =pick(Dimensionality(),$(=vYrLevel),$(=vQtrLevel),$(=vMthLevel))

Below is the link for the implemented solution, pls. check the pivot table and see if it provides expected behavior:
https://sites.google.com/site/quickdevtips/home/aggregation2.qvw?attredirects=0&d=1