Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
anothergreg
Contributor III
Contributor III

Top 2 overall in multiple dimension Line Chart

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?

4 Replies
sunny_talwar

May be use rank function with Aggr() to do this

anothergreg
Contributor III
Contributor III
Author

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!

Anonymous
Not applicable

Did you solve this problem? I'm really struggling here with the same issue

anothergreg
Contributor III
Contributor III
Author

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.