Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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
@rwunderlich Plz help here
@JordyWegman I found your similar solution for one of the questions, could you help me here?
Hi @bala_sundar ,
You can check Rank() in this link.
If you have some data it would be easier to help you.
Jordy
Climber
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.
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
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.
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
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?
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