Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
ofzhicoo
Not applicable

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?

Thanks for your help!

1 Solution

Accepted Solutions
sunny_talwar
Not applicable

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(

    {<

          Category = {'Bread'},

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

    >}

Value) = 0, 0.000000001, Sum(

    {<

          Category = {'Bread'},

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

    >}

Value))

Capture.PNG

21 Replies
sunny_talwar
Not applicable

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

Try this expression

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


Capture.PNG

ofzhicoo
Not applicable

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,1,15,Bread

A,2,20,Bread

A,3,24,Milk

B,1,12,Bread

B,2,15,Milk

B,3,16,Milk

C,1,9,Milk

C,2,8,Bread

C,3,7,Bread

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(

     {<

          Category = {'Bread'},

          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.

sunny_talwar
Not applicable

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

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

Sum(

     {<

          Category = {'Bread'},

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

     >}

Value)

sunny_talwar
Not applicable

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

Capture.PNG

ofzhicoo
Not applicable

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.

linechart.PNG

sunny_talwar
Not applicable

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

All you need is to uncheck 'Include Zero Values'

Capture.PNG

ofzhicoo
Not applicable

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,1,15,Bread

A,2,20,Bread

A,3,24,Milk

B,1,12,Bread

B,2,15,Milk

B,3,16,Milk

C,1,9,Milk

C,2,8,Bread

C,3,7,Bread

linechart.PNG

sunny_talwar
Not applicable

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

Capture.PNG

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

ofzhicoo
Not applicable

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,1,2,Bread

A,2,0,Bread

A,3,25,Milk

B,1,12,Bread

B,2,15,Milk

B,3,16,Milk

C,1,9,Milk

C,2,8,Bread

C,3,7,Bread

linechart.PNG