**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

## No comments:

## Post a Comment