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: 
Oliver_F
Partner - Creator III
Partner - Creator III

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

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

View solution in original post

21 Replies
sunny_talwar

Try this expression

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


Capture.PNG

Oliver_F
Partner - Creator III
Partner - Creator III
Author

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

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

Sum(

     {<

          Category = {'Bread'},

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

     >}

Value)

sunny_talwar

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

Capture.PNG

Oliver_F
Partner - Creator III
Partner - Creator III
Author

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

All you need is to uncheck 'Include Zero Values'

Capture.PNG

Oliver_F
Partner - Creator III
Partner - Creator III
Author

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

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

Oliver_F
Partner - Creator III
Partner - Creator III
Author

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