Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

Top 20% Sum

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!

1 Solution

Accepted Solutions
Highlighted
MVP
MVP

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))

View solution in original post

2 Replies
Highlighted
MVP
MVP

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))

View solution in original post

Highlighted
Not applicable

It worked without a problem,

Thank you very much Swuehl

Regards!