Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All, I am having some issues trying to figure out the dimension limits and/or ranking/topn function.
I want to create 2 tables - TOP 5 and BOT 5 which should yeild results exactly how its shown on the image above. Whenever I change the actual dimension limits it does absolutely nothing. I've tested on a some other data and noticed it hardly limits the dimension if you have more than 1 dimension. I really need help to show just the rows that I have drawn out. Any help is appreciated.
I was able to figure this out by updating my syntax to show -
sum(aggr(if(rank(sum([Net Gain]))< 12, sum([Net Gain])), [Activity]))
When I do <= 10, it for some reason returns only 9, but for now the workaround is implementing <12. This is currently working now.. not a complete solution but a temporary one for now.
Works for me, see app attached
It is still not working for me. The attached sample was an example in relation to my current data set. Exact same set up, 2 dimensions and a measure column. Upon implementing a top and bot 5, I still get extra rows and more than 5.. I'm completely lost. Do I need to use an aggr, rank/topn? My measure is a calculated field which I don't think should make a difference of the dimension limit capability working.
Hey Lisa, so for now what I did was take out the "Type" column which then gives ms exactly 10 rows for top and bottom 10. Once I throw in the "Type" column is when it starts messing everything up. Mind you - this was a sample data. My real data is set up exactly the same. 2 dimensions and 1 measure in a straight table. The measure is a calculated expression. Not sure what the relationship is between having a calculated dimension and trying to use the dimension limits in qlik. I'm pretty baffled on how qlik is behaving with dimension limits since I need that 3rd column in to label what that row entails for end users.
I was able to figure this out by updating my syntax to show -
sum(aggr(if(rank(sum([Net Gain]))< 12, sum([Net Gain])), [Activity]))
When I do <= 10, it for some reason returns only 9, but for now the workaround is implementing <12. This is currently working now.. not a complete solution but a temporary one for now.