Help with Top N Timeseries line chart

[#1]

I'm very new to Qlik Sense and I've hit a bit of a roadblock that I am hoping someone can help me with.

I'm trying to create a timeseries line chart with time on the x axis, the measurement on the y axis and a series for each category that I have. The problem is that I have hundreds of categories and I only want to display the top N categories where top is defined as the sum of the measurement for each category over the selected time period.

I've been playing around with Set Analysis to try and achieve what I want but I can't seem to get one that works correctly. The closest I got was using the following as my measurement:

if (aggr(RANK(SUM({$} my_measurement)), my_category) <= 10, SUM([my_measurement]))

But the above only gives me a data point per top series, and I need the complete time series displayed for each of the top N series.

Has anyone else done this already or have any suggestions?

Thanks,

Caleb

[sunny_talwar]

Would you be able to share a sample with your expected output?

[#3]

Hi Sunny,

My input would be something like this:

timestamp | my_measurement | my_categories

1/1/2016    | 1                         | A

1/2/2016    | 2                         | A

1/3/2016    | 3                         | A

1/4/2016    | 4                         | A

1/1/2016    | 3                         | B

1/2/2016    | 3                         | B

1/3/2016    | 3                         | B

1/4/2016    | 3                         | B

1/1/2016    | 2                         | C

1/2/2016    | 3                         | C

1/3/2016    | 4                         | C

1/4/2016    | 5                         | C

Let's say I want to plot the top 2 from Jan 1, 2016 to Jan 4, 2016, I would first need to determine the ranks by summing up the measurements over the period by category. Something like:

SUM(my_measurement) | my_categories | rank

10                                 | A                    | 3

12                                 | B                    | 2

14                                 | C                    | 1

Based on the above, my top 2 chart should contain a series for category C and one for category B. I then want to plot C and B on a line chart with timestamp as the x axis and my_ measurement as the y axis.

[sunny_talwar]

What if you do this:

If(Rank(Sum(TOTAL <my_category> Aggr(Sum(my_measurement), my_category, timestamp)), 1, 1) <= 10, Sum(my_measurement))

[#5]

Hi,

Create line graph with dimension- my_category and Expression - sum(my_measurement). In sort tab, uncheck all options except expression & select Descending from dropdown (also put sum(my_measurement) in its expression)

After this, in Dimension Limit Tab check limit -> select "Show only" -> First 2. Also uncheck "Show Others".

Hope this fulfill your requirement.

Thanks!

[#6]

Thanks Sunny,

It is now showing me the correct line chart but with all the categories still in the legend. So from my example above, it's showing lines for B and C, but A is also showing up in the legend. I unchecked "Show zero values" in the data handling section but I couldn't get A to disappear. Any ideas?

[sunny_talwar]

Would you be able to share a screenshot of what you are seeing?

[#8]

Hi Neha,

Thanks for the response. It's not quite working the way I expected though. I have a pie chart on the same sheet that is \defined with a dimension of my_category and a measure of Sum(my_measurement). I'm using this as my "source of truth" to determine what the actual top N are for the selected time period. When I use your method listed above, I get all the protocols listed in the legend and a different ordering of the Top ones, so I think Top is being calculated differently than I wanted.

[#9]

Below is a screenshot of what I am seeing (I set the limit to 2, just to make it work with my sample data). As you can see, the contents of the chart is correct (the lines for B and C are displayed and the one for A is not displayed) but A is still displayed in the legend.

[sunny_talwar]

Don't have too much familiarity with Qlik Sense. May be reddys310‌ can help