Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Highlighted
Not applicable

Dynamic Grouping by Rank @Qlikview

 

Hello everyone

I have a problem with grouping of data. My idea is to show in a bar chart the revenues of Top 10, Top 20, and Top 30 etc companies.

Goal is to have a “clustered” output. As dimension, grouped companies (“Top 10”, “Top 20”, “Top 30”, …) would be perfect.

The data is structured as follows: 

Company

Revenue

A

100

A

200

B

300

C

50

 

As example one of the used expressions:

=if(Rank(Sum([Revenue]))<=20 and Rank(Sum([Revenue]))>10, sum([Revenue]))”

I tried the class function and worked with dynamic dimensions – nothing worked the way I wanted.

To guarantee dynamic selection I do not want to group it within the load script.

Thanks for any idea - wish you a great Sunday!

Example of the output needed:

   

1 Solution

Accepted Solutions
Not applicable

Re: Dynamic Grouping by Rank @Qlikview

=AGGR(CLASS(RANK(SUM([Revenue])),10,SUM([Revenue])),Company)

It works - if there is another way how to handle it - I'd be happy for any suggestion!

Thanks

View solution in original post

4 Replies
Not applicable

Re: Dynamic Grouping by Rank @Qlikview

=AGGR(CLASS(RANK(SUM([Revenue])),10,SUM([Revenue])),Company)

It works - if there is another way how to handle it - I'd be happy for any suggestion!

Thanks

View solution in original post

Not applicable

Re: Dynamic Grouping by Rank @Qlikview

Hi,

Any chance of an example of this in a simple QV Document?

Thanks

Not applicable

Re: Dynamic Grouping by Rank @Qlikview

Hi Tyrone,

i dont find the function to upload a QV document, so I attached some screenshots.

Its not the perfect way, but it will group the revenues of companies.

I used "Company" as list of company names and "Revenue" as the revenue of the company.

At the moment I dont have any access to my final document - just built you a fast example.

It will group the the Top 10, Top 20 and Top 30 companies and other companies as others.

Hope it helps.

Please define the dimension as =if(AGGR(CLASS(RANK(SUM([Revenue]))-1,10,SUM([Revenue])),[Company])<=20,AGGR(CLASS(RANK(SUM([Revenue]))-1,10,SUM([Revenue])),Company))

Example_3.JPG

It will look like this:

Example_2.JPG

The final result will look like this:

Example_1.JPG

Not applicable

Re: Dynamic Grouping by Rank @Qlikview

Thank you very much for the quick reply. I will go and see what damage I can do