Qlik Community

Ask a Question

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

cancel
Showing results for 
Search instead for 
Did you mean: 
bala_sundar
Contributor III
Contributor III

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

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?

Labels (1)
  • Other

1 Solution

Accepted Solutions
JordyWegman
Partner
Partner

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

Work smarter, not harder

View solution in original post

10 Replies
bala_sundar
Contributor III
Contributor III
Author

@rwunderlich Plz help here

bala_sundar
Contributor III
Contributor III
Author

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

JordyWegman
Partner
Partner

Hi @bala_sundar ,

You can check Rank() in this link.

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

Jordy

Climber

Work smarter, not harder
bala_sundar
Contributor III
Contributor III
Author

Hi @JordyWegman ,

Thanks a lot for your prompt response.

The sample data would be like below,

bala_sundar_0-1627291495819.png

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

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

Work smarter, not harder
bala_sundar
Contributor III
Contributor III
Author

Hi Jordy,

Thanks for your reply.

lets assume the below table,

bala_sundar_0-1627623757852.png

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

bala_sundar_1-1627623861965.png

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

bala_sundar_2-1627624058099.png

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

JordyWegman
Partner
Partner

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.

Dim1Dim2M1 * 1000M2M1 + 2
A1010.0005010.050
A1010.0004810.048
B99.000959.095

 

This way your sorting will be done correctly.

Jordy

Climber

Work smarter, not harder
bala_sundar
Contributor III
Contributor III
Author

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

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

Work smarter, not harder

View solution in original post