topic Re: QlikView - Sum top 8 values only even though values beyond 8 are also equal to the 8th value in App Development

QlikView - Sum top 8 values only even though values beyond 8 are also equal to the 8th value

NickBentley — Tue, 18 Jun 2024 06:56:15 GMT

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

Re: QlikView - Sum top 8 values only even though values beyond 8 are also equal to the 8th value

dmac1971 — Tue, 18 Jun 2024 07:25:30 GMT

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)

https://help.qlik.com/en-US/qlikview/May2024/Subsystems/Client/Content/QV_QlikView/ChartFunctions/RankingFunctions/rank.htm#:~:text=Rank()%20evaluates%20the%20rows,dimension%20evaluated%20in%20the%20expression.

Re: QlikView - Sum top 8 values only even though values beyond 8 are also equal to the 8th value

NickBentley — Wed, 19 Jun 2024 00:11:27 GMT

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?