How to get a sum for the top 5 drugs by account?

[Steve_Rosebrook]

I'm trying to create a table of healthcare account subgroup names, along with the total allowed amount for the top 5 specialty drugs in each account.  I think I need to use some combination of rank and aggr, but nothing I've tried has worked.

The type of table I'm trying to create is below:

Obviously things aren't working correctly.  Each subgroup with a total in the 2nd column should have some kind of non-zero total in the 3rd column - but that's definitely not the case.  Here's the current expression I have for the "Allowed - Top 5 Specialty Drugs" column:

=if(aggr(rank(SUM({<[Specialty Drug Indicator]={'Y'}>}[Amount Allowed]),4,1),[Account SubGroup],[Pharmacy Drug Name])<=5  , SUM({<[Specialty Drug Indicator]={'Y'}>}[Amount Allowed])  , 0)

I've experimented with using rank/aggr and aggr/rank, as well as trying different modes/fmts in the rank function - and also different options in the aggr function.

Basically for a given Account SubGroup, I want to show SUM({<[Specialty Drug Indicator]={'Y'}>}[Amount Allowed]) for the top 5 Specialty Drugs in that Account SubGroup.

Any help would be much appreciated - I'm tearing my hair out at this point.  Thanks!

[Oleg_Troyansky]

Hi Steve,

the "magic" combination of functions is this:

SUM( AGGR( IF( RANK(.....)., ....), Dim1, Dim2))

This should produce the result that you are looking for.

Allow me to invite you to the Masters Summit for Qlik, where I teach solutions like this one at my Advanced Set Analysis and AGGR session, along with a multitude of other advanced development techniques for Scripting, Data Modeling, Visualizations, Performance, and more! We will be in Orlando and in Dublin, Ireland this fall. See if you can join.