Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am running into issues with the dataset below where my expression to find the aggregate of the top 8 values only is actually summing the top 15 values as values 8 through to 15 all equal 2.
I am expecting to get a sum of 26 (top 8 values only) yet 40 is being returned.
=Sum(Aggr(if(Rank(Sum(Units),1,1) <= 8,Sum(Units)),Item)))
Is there a way of discarding values beyond the 8th position even though they are also equal to the 8th value?
Thanks in advance!
Week Number | Units Sold |
FY24-14 | 5 |
FY24-6 | 4 |
FY24-27 | 3 |
FY24-28 | 3 |
FY24-33 | 3 |
FY24-44 | 3 |
FY24-48 | 3 |
FY24-7 | 2 |
FY24-10 | 2 |
FY24-19 | 2 |
FY24-21 | 2 |
FY24-23 | 2 |
FY24-26 | 2 |
FY24-36 | 2 |
FY24-47 | 2 |
FY23-12 | 1 |
FY23-15 | 1 |
FY23-51 | 1 |
FY24-1 | 1 |
FY24-11 | 1 |
FY24-12 | 1 |
FY24-13 | 1 |
FY24-16 | 1 |
FY24-17 | 1 |
FY24-20 | 1 |
FY24-24 | 1 |
FY24-30 | 1 |
FY24-31 | 1 |
FY24-40 | 1 |
FY24-41 | 1 |
FY24-51 | 1 |
40 is correct as Qlik is treating the 2's as equal? This should work though :
Sum({<[Week Number]={"=rank(sum(Units),4)<=8"}>} Units)
Hi dmac1971,
Thanks for providing the recommended solution above, although, the solution is now showing 14 as the answer as it seems to be ignoring duplicates now and moving on to the next value - 5+4+3+2 = 14.
I was wondering if doing a series of FirstSortedValues expressions may work (e.g. FirstSortedValue (1-8) and then adding these together?