
Re: Can I add row number to aggregate function in charts  nonstandard quartile request
Sunny Talwar Jan 5, 2017 8:47 AM (in response to Erryn Leask)Would you be able to share a sample to look at the issue?

Re: Can I add row number to aggregate function in charts  nonstandard quartile request
Erryn Leask Jan 6, 2017 6:19 AM (in response to Sunny Talwar )Thanks for the reply,
After some further investigation I have realised that the Rank function IS working  for some reason, where there are multiple entries with the same value it reverses the rank numbers so everything looks out of sync (but as they are the same values anyway it makes no difference how they are ranked) (see all values at 0.00 and 36.68 in attached)
Onto the overall issue of using this to split the population into 4 equal groups. I have attached my sample project, filtered on Base Pay for now. There are 3 key attempts ('Quartiles' tables):
1) Using Qlik's Fractile function  you can see that the popuation of 100 is not split into 4 groups of 25 as desired
2) My first test, using the rank function within aggregate and hardcoding the quartile populations into blocks of 25 to check the first aggr(rank()) works
3) Trying to calculate the population which needs to be variable depending on filter/new client data, using the calculation below:
='Pay band '&if(
aggr(Rank(sum(Amount), 4),[StaffNo]) <= (count(distinct(StaffNo))/4), 'A (Lowest)',
if(aggr(Rank(sum(Amount), 4),[StaffNo]) <= ((count(distinct(StaffNo))/4)*2), 'B',
if(aggr(Rank(sum(Amount), 4),[StaffNo]) <= ((count(distinct(StaffNo))/4)*3),'C', 'D (Highest)'))
)
I fell like I must be missing something very obvious!
Thanks!

Quartiles.qvw 167.5 K

Re: Can I add row number to aggregate function in charts  nonstandard quartile request
Sunny Talwar Jan 6, 2017 8:27 AM (in response to Erryn Leask)May be this:
='Pay band '&if(
aggr(Rank(sum(Amount), 4),[StaffNo]) <= (count(TOTAL distinct(StaffNo))/4), 'A (Lowest)',
if(aggr(Rank(sum(Amount), 4),[StaffNo]) <= ((count(TOTAL distinct(StaffNo))/4)*2), 'B',
if(aggr(Rank(sum(Amount), 4),[StaffNo]) <= ((count(TOTAL distinct(StaffNo))/4)*3),'C', 'D (Highest)'))
)

Quartiles.qvw 163.8 K

Re: Can I add row number to aggregate function in charts  nonstandard quartile request
Erryn Leask Jan 6, 2017 10:52 AM (in response to Sunny Talwar )Thanks, the TOTAL works.
Have marked appropriately, thanks again


