Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am trying to create a Line Chart with multiple dimensions that will provide results on the Top 2 overall rather than the Top 2 intersect of the two dimensions. Currently using Dimension Limits I am only able to see the Top 2 intersect of Dimension Limits, however if I change the chart to a Straight Table and sort using the second dimension (the one I would like to use as the overall criteria) I'm able to see it without issue.
For example, say I have Dimensions:
Quarter - Period in the year
State - State in which the transaction took place
And Expressions:
Count - Invisible, no Display, used as calculated field for "first expression" calculations only
Count(TransactionID)
Frequency - % of all Transactions
Count(TransactionID)/Count(TOTAL TransactionID)
My Dimension Limits is set to the Top 2 States only. My expectation here is that Qlik will take a look at all of the States in the given selection, find the States with the largest 2 Count(TransactionID), and give me two lines representing these two States over each Quarter. Instead, Qlik is finding the Top 2 States per Quarter, which leaves me with more lines than I would like.
In the attached example, you can see that there are 3 lines in the workspace representing FL, GA, and NY. This is because in Q1 FL&NY have the highest Count(TransactionID), Q2 belongs to FL&NY, Q3 belongs to GA&NY, and Q4 belongs to GA&NY.
The Straight Table below the Line Chart is just a copy of the same object, but with a sort on State. Because it's doing the sort only on State, we only see two States, which is what I'd like to see represented in the Line Chart. You can change it to look like the Line Chart above if you change it to sort on Quarter, where we will then have 3 states.
I've tried multiple sorting options but I can't ever see less than 3 lines in the Line Chart. Is there a fix to this that I'm missing?
May be use rank function with Aggr() to do this
The only issue with this is in my actual data I'm doing a Top 5 of 52 possibilities so I wouldn't have a legend limited to the Top 5 and it would be confusing to see entries outside of the ones visible in the chart. Your result in the actual Line Chart is what I'm looking for, however!
Did you solve this problem? I'm really struggling here with the same issue
Unfortunately not. This might have to be solved with logic in the data model by creating a "top X" flag in the load script, but I never went through with it.