Aggr + rank displayed over time

[Jonathan_Alm]

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:

Year	Product Category	Sale Product	Avg(Sales)
2019	A	Iron	1000
2018	A	Glass	1400
2020	A	Sand	1500
2020	B	Shoes	2000
2019	B	Socks	1400
2017	B	Shirts	1500
2019	B	Socks	1500
2019	B	Jackets	1000
2017	B	Gloves	700

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

[Kushal_Chawda · Accepted solution]

@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 · Accepted solution]

@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 :

[Jonathan_Alm]

Thanks, that worked out perfectly!

Kind regards, Jonathan

[Jonathan_Alm]

@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]

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".

[sunny_talwar]

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

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

[Kushal_Chawda · Accepted solution]

@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