Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
kira_whopper
Creator
Creator

Count of Values from Rank > n

Hi there,

I need to show in a chart the Sum of Sales of some Corporations and a Rank based on the limit defined in a slider (set in Dimension Limit). But the rank must consider the first n values set on the slider + the values of the Corporations of a group of Corporations I defined.

For example:

Cor1

Cor2

Cor3

Cor4

Cor5

Cor6

Cor7

Cor8

Cor9

Cor10


If the Slider is set to 5, and the group of Corporations I'm trying to show considers Corporations 7 and 9, it should be like this:

Cor1

Cor2

Cor3

Cor4

Cor5

Cor7

Cor9


But this could nof influence the position of each Corporation in the Rank. Ok, this works like a charm in the Rank expression I made, but if I use that same expression to verify where there sould be Sales (setting to null() when not applicable), QlikView brakes the Others line, not showing the value that should be in it.


What I need for now is a way to count these Corporations outside the Slider value to set in the Dimension Limit, where the Others row works properly. Or a better way to do this.

This issue should work with tables and bar/line charts.


I'm attaching a qvw file and the xls I used to develop this test case.

Regarding the script, the real case uses a very big fact table with multiple considerations for the rank for each period and dimension, so I do't think that would be a solution.


Thanks in advance.


1 Solution

Accepted Solutions
kira_whopper
Creator
Creator
Author

Managed to do it with a calculated dimension, instead of Corporation:

=IF(aggr(rank(TOTAL sum(Value)), Corporation) <= 10 OR Corporation = 'Corp7' OR Corporation = 'Corp9', aggr(rank(TOTAL sum(Value)), Corporation), 'Others')

Then, for the sorting to don´t be messed up, used this ascending:

=IF(aggr(rank(TOTAL sum(Value)), Corporation) <= 10 OR Corporation = 'Corp20' OR Corporation = 'Corp10', aggr(rank(TOTAL sum(Value)), Corporation), 9999)

Where the 9999 would be a safe limit that wouldn't be reached by the slider.

Attached file with solution.

View solution in original post

1 Reply
kira_whopper
Creator
Creator
Author

Managed to do it with a calculated dimension, instead of Corporation:

=IF(aggr(rank(TOTAL sum(Value)), Corporation) <= 10 OR Corporation = 'Corp7' OR Corporation = 'Corp9', aggr(rank(TOTAL sum(Value)), Corporation), 'Others')

Then, for the sorting to don´t be messed up, used this ascending:

=IF(aggr(rank(TOTAL sum(Value)), Corporation) <= 10 OR Corporation = 'Corp20' OR Corporation = 'Corp10', aggr(rank(TOTAL sum(Value)), Corporation), 9999)

Where the 9999 would be a safe limit that wouldn't be reached by the slider.

Attached file with solution.