How to aggregate top 20

[msapre]

I'm trying to find the total PARHELD for the top 20 companies in the dataset for each date.

The variable PARHELD is the total that I'm trying to calculate. 

I've made a table with sum(PARHELD) as the measure and DATE as the dimension. What I want is for the sum(PARHELD) column to only display the sum(PARHELD) for the top 20 companies for each DATE for the selected issuer (IssueFilter is the dynamic variable I've created for this purpose) (the top 20 is calculated by highest PARHELD). 

I tried the following set expression, but it isn't working:

Sum({<COMPANY = {"=Rank(Sum({<ISSUERCODE = {'$(=[IssueFilter])'}>}PARHELD))<=20"}, ISSUERCODE = {'$(=[IssueFilter])'}>} PARHELD)

How do I fix this so I get the right values for the sum of the top 20 companies for each date for the selected issuer?

Thanks!

[Oleg_Troyansky · Accepted solution]

Hi there,

The problem here is that you need to rank companies per Date,  which is something that can't be done with Set Analysis. Your Set Analysis, otherwise perfectly fine, can help you rank companies overall, without regard to dates, but not for each date.

In order to rank Companies for each Date, you'll need to use the AGGR() function by Date and Company, and verify your condition using the IF() function. Something along these lines:

{<ISSUERCODE = {'$(=[IssueFilter])'}>}
SUM(

     AGGR(

          IF(Rank(Sum(PARHELD))<=20, 
               Sum(PARHELD))
          , Date, ISSUERCODE)
)

I'm teaching exactly this solution as one of the exercises in my session on Set Analysis and AGGR. Allow me to invite you to the upcoming Masters Summit for Qlik in Hamburg or to my upcoming Qlik Expert Class in Vienna. You will learn many advanced development techniques like this one!

[msapre]

Hi Oleg,

Thanks for your quick response. Unfortunately, this solution isn't giving me the right sum. It's calculating a value that's way higher than what it should be.

[Oleg_Troyansky]

Hi Msapre,

Well, now you need to figure out why. You'll need to tell us a lot more details than that, if you want anyone's help with it.

[msapre]

Thanks Oleg,

I was able to figure out the issue - it was on the data side and not a Qlik problem.