Top 2 overall in multiple dimension Line Chart

anothergreg — Wed, 25 Nov 2020 16:16:04 GMT

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?

Re: Top 2 overall in multiple dimension Line Chart

sunny_talwar — Thu, 20 Jul 2017 16:16:12 GMT

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

Re: Top 2 overall in multiple dimension Line Chart

anothergreg — Thu, 20 Jul 2017 16:21:28 GMT

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!

Re: Top 2 overall in multiple dimension Line Chart

Anonymous — Thu, 12 Apr 2018 15:27:46 GMT

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

Re: Top 2 overall in multiple dimension Line Chart

anothergreg — Thu, 12 Apr 2018 15:46:27 GMT

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.

topic Re: Top 2 overall in multiple dimension Line Chart in QlikView

Top 2 overall in multiple dimension Line Chart

Re: Top 2 overall in multiple dimension Line Chart

Re: Top 2 overall in multiple dimension Line Chart

Re: Top 2 overall in multiple dimension Line Chart

Re: Top 2 overall in multiple dimension Line Chart