21 Replies Latest reply: Dec 22, 2017 7:39 AM by Sunny Talwar

# Linechart show Top 2 based on an other (!) Keyfigure

Hi There,

My data looks like this:

Customer, Month, Value1, Value2

A,1,15,1

A,2,20,1

A,3,24,1

B,1,12,2

B,2,15,2

B,3,16,2

C,1,9,2

C,2,8,2

C,3,7,2

This data changes from time to time.

My goal is to show a linechart with the Dimension Customer and Month and the Keyfigure Value1. But I only want to show the top 2 customers based on Value2.

In my example the linechart should only show Customer B and C as the have the biggest Values for Value2.

I tried to create a dynamic dimension using if and rank, but this did not work as expected.

Do you have any idead how to solve this?

• ###### Re: Linechart show Top 2 based on an other (!) Keyfigure

Try this expression

Sum({<Customer = {"=Rank(Sum(Value2)) < 3"}>}Value1)

• ###### Re: Linechart show Top 2 based on an other (!) Keyfigure

Hi Sunny,

I like the idea of moving the filter from the dynamic dimension to the Set Analysis. Your approach clearly works for the basic scenario.

Unfortunately my actual use case is a little bit more complex and I am not able to transfer your idea to my real world problem.

1) I am using QlikSense and not QlikView.

2) My ranking is not only based on a simple formular like Sum(Value2). Instead it has its own SetAnalysis included.

Please have a look at my updated example data:

Customer, Month, Value, Category

A,3,24,Milk

B,2,15,Milk

B,3,16,Milk

C,1,9,Milk

What I woul like to do is: Show a line Chart for Bread for the two Customers that have bought the most milk.

So the rank function would be something like this:

sum(

{<

Customer = {"=rank({<Category = {'Milk'}>}sum(Value))<3"}

>}

Value)

I tried to put this in a variable, but this did not solve my problem neither. Furthermore the Set Analysis will have more parameters in the final app.

• ###### Re: Linechart show Top 2 based on an other (!) Keyfigure

Try this... Set Analysis needs to be within the Sum function

Sum(

{<

Customer = {"=Rank(Sum({<Category = {'Milk'}>}Value)) < 3"}

>}

Value)

• ###### Re: Linechart show Top 2 based on an other (!) Keyfigure

It should work the same way in Qlik Sense.... Here is an image from QlikView

• ###### Re: Linechart show Top 2 based on an other (!) Keyfigure

Hey Sunny,

thanks for your help! Unfortunately this does not work in QS.

The third customer ist still present in the legend and shown in the bar as a zero-value-line.

• ###### Re: Linechart show Top 2 based on an other (!) Keyfigure

All you need is to uncheck 'Include Zero Values'

• ###### Re: Linechart show Top 2 based on an other (!) Keyfigure

Hey Sunny,

that is a very good idea, but this won't work for me as my original data has a many more months and some values are really zero. I still need to see the zero values for the customers that are displayed in the chart.

Furthermore there is a problem with the approach.

If I want to show sum(value) (not only bread) for the two customers that hav sold the most milk. The bold marked values is missing in the chart.

Sum(

{<

Customer = {"=Rank(Sum({<Category = {'Milk'}>}Value)) < 3"}

>}

Value)

Customer, Month, Value, Category

A,3,24,Milk

B,2,15,Milk

B,3,16,Milk

C,1,9,Milk

• ###### Re: Linechart show Top 2 based on an other (!) Keyfigure

Can you show me a sample where you have the first issue?

For you second issue, I think Bread is showing up for month 1

Since it is the only value, there is a dot which might not be very clearly visible

• ###### Re: Linechart show Top 2 based on an other (!) Keyfigure

Hey Sunny,

you are right. I had to set the y-axis to start at zero.

Regarding the other problem I adjusted the example data:

A,3,25,Milk

B,2,15,Milk

B,3,16,Milk

C,1,9,Milk

• ###### Re: Linechart show Top 2 based on an other (!) Keyfigure

I don't perfectly understand Qlik Sense, but this sort of works, but legend and color went away

Dimension

Month

=Aggr(If(Rank(Sum({<Category = {'Milk'}>}Value)) < 3, Customer), Customer)

Expression

Sum(

{<

Customer = {"=Rank(Sum({<Category = {'Milk'}>}Value)) < 3"}

>}

Value)

Uncheck 'Include Null values' for the second dimension and keep 'Include Zero Values' from the Data Handling section

• ###### Re: Linechart show Top 2 based on an other (!) Keyfigure

Hey Sunny,

that is a nice solution, but I am afraid, that this won't work for my project. Because the chart become pretty useless if I want to present top 4 and I do not have any color and legend that describes the line.

• ###### Re: Linechart show Top 2 based on an other (!) Keyfigure

I knew this would be the case, but I guess I am not using Qlik Sense that much to give you solution to all possible things... I can give you work arounds, but I am sure you will find some issues with those also ... I will make one last attempt... if you still don't like it, then some body else will have to help you.

Best,

Sunny

• ###### Re: Linechart show Top 2 based on an other (!) Keyfigure

Try this expression

If(Rank(TOTAL Sum({<Category = {'Milk'}>}Value)) < 3 and Sum(

{<

Customer = {"=Rank(Sum({<Category = {'Milk'}>}Value)) < 3"}

>}

Value) = 0, 0.000000001, Sum(

{<

Customer = {"=Rank(Sum({<Category = {'Milk'}>}Value)) < 3"}

>}

Value))

and once again uncheck 'Include Zero Values' under Data Handling and change your dimension back from the calculated one to just Category.

• ###### Re: Linechart show Top 2 based on an other (!) Keyfigure

Hey Sunny,

I really appreciate your help! But this solution is missing the values for the second month.

I guess I have to find another approach and maybe precalculate the values in the datamodel.

Thank you very very much! I will let you know if I find another solution!

All the best

Oliver

• ###### Re: Linechart show Top 2 based on an other (!) Keyfigure

That was my bad, so yet another final try

If(Aggr(NODISTINCT Rank(Sum({<Category = {'Milk'}>}Value)), Customer) < 3 and Sum(

{<

Customer = {"=Rank(Sum({<Category = {'Milk'}>}Value)) < 3"}

>}

Value) = 0, 0.000000001, Sum(

{<

Customer = {"=Rank(Sum({<Category = {'Milk'}>}Value)) < 3"}

>}

Value))

• ###### Re: Linechart show Top 2 based on an other (!) Keyfigure

Hey Sunny,

this really does not seem to be a normal feature in QS so I do not want to waste your time

This still is not the final resolution, as the bold marked items are now shown as zero instead of their actual values.

A,3,25,Milk

B,2,15,Milk

B,3,16,Milk

C,1,9,Milk

I will try to figure out something by myself and let you know if I get along with it.

• ###### Re: Linechart show Top 2 based on an other (!) Keyfigure

But those are Milk, you specifically told your expression to look at Category = 'Bread'?

If(Aggr(NODISTINCT Rank(Sum({<Category = {'Milk'}>}Value)), Customer) < 3 and Sum(

{<

Customer = {"=Rank(Sum({<Category = {'Milk'}>}Value)) < 3"}

>}

Value) = 0, 0.000000001, Sum(

{<

Customer = {"=Rank(Sum({<Category = {'Milk'}>}Value)) < 3"}

>}

Value))

If you don't want that condition, may be you want to remove it?

• ###### Re: Linechart show Top 2 based on an other (!) Keyfigure

This is what I get when I use this expression

If(Aggr(NODISTINCT Rank(Sum({<Category = {'Milk'}>}Value)), Customer) < 3 and Sum(

{<

Customer = {"=Rank(Sum({<Category = {'Milk'}>}Value)) < 3"}

>}

Value) = 0, 0.000000001, Sum(

{<

Customer = {"=Rank(Sum({<Category = {'Milk'}>}Value)) < 3"}

>}

Value))

• ###### Re: Linechart show Top 2 based on an other (!) Keyfigure

Also, as long as it is something related to numbers, you can continue to waste my time . It is Qlik Sense specific layout things and display things that I am not 100% sure about

• ###### Re: Linechart show Top 2 based on an other (!) Keyfigure

Hey Sunny,

a thousand thanks for your help! This indeed looks pretty good. I will now have to figure out if and how this will work with my actual data that needs much more complex calculations.

Once again thank you very much for your input.

• ###### Re: Linechart show Top 2 based on an other (!) Keyfigure

Awesome, I am glad we were able to get a break through