Count of Values from Rank > n

kira_whopper — Thu, 17 Mar 2016 13:07:13 GMT

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.

Re: Count of Values from Rank > n

kira_whopper — Thu, 17 Mar 2016 17:31:26 GMT

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.

topic Re: Count of Values from Rank > n in QlikView

Count of Values from Rank > n

Re: Count of Values from Rank > n