What if the measure used in calculated on measure in dimension limitation has duplicates?

[bala_sundar]

Hi All,

I want to limit my dimension by top 10. When i use limitation in dimension property, it uses the first measure in the table as calculated on measure which is what i also want to do. But the measure used has duplicates. In such case, i want this look after another measure. But i am not sure on what basis it limits the dimension when the measure has duplicates.

Ex: I want to limit the team based on their total score. When all the teams are in same total, i want this to look at no.of fours and if that too same, it should look at no of sixes and so on. 

How to achieve this in Qliksense?

[bala_sundar]

@rwunderlich Plz help here

[bala_sundar]

@JordyWegman I found your similar solution for one of the questions, could you help me here?

[JordyWegman]

Hi @bala_sundar ,

You can check Rank() in this link.

If you have some data it would be easier to help you.

Jordy

Climber

[bala_sundar]

Hi @JordyWegman ,

Thanks a lot for your prompt response.

The sample data would be like below,

I need to limit Dim2 to top 5 for each value in Dim1 based on sum(Measure1) but since all the values are same, it randomly picks 5. But in such case i want this to look at sum(Measure2) to take top 5.

Bala.

[JordyWegman]

Hi Bala,

In that case I would rank on the sum of Sum(Measure1) + Sum(Measure2). This way you will incorporate the right ranking.

Though, could show also an example of your desired outcome so we both know what the end solution should be?

Jordy

Climber

[bala_sundar]

Hi Jordy,

Thanks for your reply.

lets assume the below table,

My expected outcome should be below, when the measure 1 is same for all, it should look at measure 2 to take top 5 for each item in Dim1

But if I follow your way, rank on the sum of Sum(Measure1) + Sum(Measure2), getting incorrect output

Its taking 9 in Measure 1 when it has 10 which should be the top 5.

[JordyWegman]

Hi Bala,

Any idea on how big the numbers are going to get? Otherwise I would suggest to multiply Measure1 in order to get rid of your last issue. For instance by a 1000.

Dim1	Dim2	M1 * 1000	M2	M1 + 2
A	10	10.000	50	10.050
A	10	10.000	48	10.048
B	9	9.000	95	9.095

This way your sorting will be done correctly.

Jordy

Climber

[bala_sundar]

Hi Jordy,

I think this will work. But I can't use the calculated or rank columns in the view, in such case how to use limit when i have only the 2 measures in the view?

[JordyWegman · Accepted solution]

Hi Bala,

In that case you need to aggregate your dimension:

Aggr( 
    If( Rank( Sum(Measure1 * 1000) + Sum( Measure2 ) ) <=5, 
         Dim1 & Dim 2 ),  
Dim1 & Dim 2 )

Where I've placed Dim1 & Dim2, you need to decide which dimensions you want to add. In order to get this to work you need to remove empty dimension values in your chart.

Jordy

Climber