Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I would like to limit dimension values on a chart so that it's part based on a measure value and part of it is fixed. Let's suppose we have the following table:
Field 1 | Value |
A | 10 |
B | 40 |
C | 5 |
D | 80 |
1 | 40 |
2 | 20 |
3 | 60 |
4 | 100 |
I'd like values A, B, C and D to be always displayed in a dimension plus the top 2 from the remaining values, so in this case it would be 3 and 4 (so my dimension should display A, B, C, D, 3, 4). How could this be achieved?
Thanks,
Laura
May be this
RangeSum( Sum({<Field1 = {'A', 'B', 'C', 'D'}>} Value), Sum({<Field1 = {"=Rank(Sum({<Field1 -= {'A', 'B', 'C', 'D'}>}Value)) < 3"}>} Value) )
Or even this
Sum({<Field1 = {"=Rank(Sum({<Field1 -= {'A', 'B', 'C', 'D'}>}Value)) < 3"}+{'A', 'B', 'C', 'D'}>} Value)
Thank you Sunny that works great!
Just wondering if I want the calculation in my measure to be a share calculation, so e.g. the share of each value in the Field1 column out of the total, is there a way for that calculation to ignore the limitation in that dimension?
Given that the dimension has been limited to A, B, C, D, 3 and 4, Qlik calculates the total based on these values, rather than A, B, C, D, 1, 2, 3, & 4.