topic Top 20% Sum in QlikView

Top 20% Sum

Thu, 02 Jul 2015 22:19:29 GMT

Good Afternoon Qlik Community,

I'm developing a chart in QV in which I want to see the SUM of the Values of the TOP 20% of observations with a time dimention (MONTHYEAR).

Specificially I want to compare the TOP 20% of one month against the TOP 20% of other months (every month shows its own TOP 20% observations).

I'm using the expression:

Sum({<REGISTER={"=(rank(sum(Value)))<=(ceil(count(TOTAL<MonthYear> distinct (REGISTER))*0.20))"}>}Value)

And it's Working!

Unfortunately it only works when I have only one MONTH selected. When I pick more than one MONTH it goes in a different way which I haven't figured out.

Any help is good help.

Thank you all

Regards!

Re: Top 20% Sum

swuehl — Thu, 02 Jul 2015 22:40:16 GMT

The set expression is only evaluated once per chart, not per dimension value (i.e. per MONTHYEAR).

Try something like

=Sum( Aggr(

if( Rank(Sum(Value)) <= Ceil(Count( TOTAL<MonthYear> DISTINCT REGISTER)*0.20),Sum(Value))

,MonthYear, REGISTER))

Re: Top 20% Sum

Thu, 02 Jul 2015 22:48:34 GMT

It worked without a problem,

Thank you very much Swuehl

Regards!