Percentile Measure for Sales?

[MassicotPSCU]

Hi,

I have sales data table, where records are added for the amount of sales each representative has by date.  I also have a calendar table to get the field Year-Month. While using the sum of sales for all representatives combined, I need to find out where each representative's individual sales lands percentile wise in comparison to that sum of sales for each Year-Month.  I'm looking specifically for the 20th percentile, 40th percentile, 60th percentile and 80th percentile.  

Once I find out which percentile each representative belongs to, then I need to do an if statement to give it a score from 1-5. If the representative is lower than the 20th percentile a score of 1 is given. If the representative is lower than the 40th percentile but higher than the 20th percentile a score of 2 is given, and so on.  

To support my current data model, I need this done as a measure not in the load script.

Here is what I have so far along with my sample data. The problem with this expression is, it doesn't seem to bring me back the correct numbers when I filter by Year-Month.

{1<[Year-Month]>}if(aggr(Sum(distinct(sales)),Rep) <= fractile({1<[Year-Month]>}TOTAL aggr(Sum(sales),Rep), 0.20), 5,
{1<[Year-Month]>}if(aggr(Sum(distinct(sales)),Rep) <= fractile({1<[Year-Month]>}TOTAL aggr(Sum(sales),Rep), 0.40), 4,
{1<[Year-Month]>}if(aggr(Sum(distinct(sales)),Rep) <= fractile({1<[Year-Month]>}TOTAL aggr(Sum(sales),Rep), 0.60), 3,
{1<[Year-Month]>}if(aggr(Sum(distinct(sales)),Rep) <= fractile({1<[Year-Month]>}TOTAL aggr(Sum(sales),Rep), 0.80), 2, 1))))

[Oleg_Troyansky]

Hi,

I can see a few issues with this expression. Most importantly, it's important to understand that an AGGR() by Rep will return one single number per Rep, and because of that the results cannot be sensitive to particular Year-Month values. So, as a minimum, the AGGR() should include Year-Month as a dimension, and the aggregation that should be aggregated by month, may need a TOTAL <Year-Month> qualifier.

Secondly, this formula contains 8 separate AGGR() functions, which can't be good for performance. Perhaps you can calculate the same in a different way. It looks like you are trying to divide the whole population of Reps into 5 equally sized buckets, based on their Sales performance. This can be done a lot easier with the use of the function Rank(). 

I call this kind of analysis the "Bucket Analysis". I'm covering this example, along with many other advanced analytics, in my session on Set Analysis and AGGR() at the Masters Summit for Qlik. I will be delivering it online on March 1st. I'm inviting you to check out our agenda and to join us, to learn advanced development techniques.

Cheers,