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: 
Jonathan_Alm
Partner - Creator
Partner - Creator

Aggr + rank displayed over time

Hi,

I have a line chart that displays the total average for a produkt over time.

The calculated dimension is the following: 

=if(aggr(rank(avg([Sales])),Sale product) <=5, [Sale product], NULL()) 

This calculated dimension picks the top 5 average selling product.

I am displaying the in a line chart over time which makes these products avg sales being displayed. 

All this works fine.

But I would like to create a comparision between these products by looking at the product categories.

The table I am using looks like this:

YearProduct CategorySale ProductAvg(Sales)
2019AIron1000
2018AGlass1400
2020ASand1500
2020BShoes2000
2019BSocks1400
2017BShirts1500
2019BSocks1500
2019BJackets1000
2017BGloves700

 

My code above will display Shoes, Sand, Glass, Shirts & Socks since they have the highest average sales.

 

But I would like to create two separate Line charts. One that displays top 2 avg sales for Category A and one that does the same for Category B. 

 

How do I add so that the line chart only looks at these categories so that I can compare these two in two separate line charts? Do I add this to the calculated dimension? Or do I add this to the measure (set analysis)?

 

Kind regards, Jonathan

 

Labels (4)
3 Solutions

Accepted Solutions
Kushal_Chawda

@Jonathan_Alm  try below Individual expressions as calculated dimension of line chart

=if(aggr(rank(avg({<[Product Category]={'A'}>}[Sales])),Sale product) <=2, [Sale product], NULL()) 

=if(aggr(rank(avg({<[Product Category]={'B'}>}[Sales])),Sale product) <=2, [Sale product], NULL()) 

View solution in original post

Taoufiq_Zarra

@Jonathan_Alm  Like ?

for top 2 category A:

=if(aggr(rank(avg({<[Product Category]={'A'}>}[Sales])),[Sale Product]) <=2,[Sale Product], NULL())

category B :

=if(aggr(rank(avg({<[Product Category]={'B'}>}[Sales])),[Sale Product]) <=2,[Sale Product], NULL())

output like :

Capture.PNG

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉

View solution in original post

Kushal_Chawda

@Jonathan_Alm  you can try the same expression  like below in title

='top sold product for category A-' &concat(if(aggr(rank(avg({<[Product Category]={'A'}>}[Sales])),Sale product) =1, [Sale product], NULL()),',')

I will then get the top sold product for category A w

View solution in original post

8 Replies
Kushal_Chawda

@Jonathan_Alm  try below Individual expressions as calculated dimension of line chart

=if(aggr(rank(avg({<[Product Category]={'A'}>}[Sales])),Sale product) <=2, [Sale product], NULL()) 

=if(aggr(rank(avg({<[Product Category]={'B'}>}[Sales])),Sale product) <=2, [Sale product], NULL()) 

Taoufiq_Zarra

@Jonathan_Alm  Like ?

for top 2 category A:

=if(aggr(rank(avg({<[Product Category]={'A'}>}[Sales])),[Sale Product]) <=2,[Sale Product], NULL())

category B :

=if(aggr(rank(avg({<[Product Category]={'B'}>}[Sales])),[Sale Product]) <=2,[Sale Product], NULL())

output like :

Capture.PNG

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
Jonathan_Alm
Partner - Creator
Partner - Creator
Author

Thanks, that worked out perfectly!

Kind regards, Jonathan

Jonathan_Alm
Partner - Creator
Partner - Creator
Author

@Taoufiq_Zarra  @Kushal_Chawda 

If I would continue this dynamic idea, would it also be possible to add a dynamic lable, so that chart A contains a dynamic lable displaying the "Sale product"  and chart B displaying the Sale Product. 

So if my dynamic dimension picks top sale product for Category A I want the chart to display Sand. 

And the chart for Category B "top product" should dynamically show Shoes. (if that is the top products at that time of the load of data)

 

Kushal_Chawda

@Jonathan_Alm  Not sure I am following that. How would you like the label? label of expression or title of chart?

Jonathan_Alm
Partner - Creator
Partner - Creator
Author

Hi,

let's say I use your dynamic dimension but change the rank to = 1

=if(aggr(rank(avg({<[Product Category]={'A'}>}[Sales])),Sale product) =1, [Sale product], NULL())

I will then get the top sold product for category A which in my example is:  Sand

So the line chart will then show the average sales for that product.

I would the like the line chart to display  "Sand" in the label. Like a "GetCurrentSelection" of what is displayed in the line chart.  See below. The "title" should display what the dynamic dimension is "selecting".

Screenshot 2020-09-30 at 14.13.29.png

sunny_talwar

@Jonathan_Alm  You can use the same expression under General -> Title

image.png

=if(aggr(rank(avg({<[Product Category]={'A'}>}[Sales])),Sale product) =1, [Sale product], NULL())

 

Kushal_Chawda

@Jonathan_Alm  you can try the same expression  like below in title

='top sold product for category A-' &concat(if(aggr(rank(avg({<[Product Category]={'A'}>}[Sales])),Sale product) =1, [Sale product], NULL()),',')

I will then get the top sold product for category A w