topic Re: Aggr + rank displayed over time in App Development

Aggr + rank displayed over time

Jonathan_Alm — Sat, 16 Nov 2024 01:21:22 GMT

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

Re: Aggr + rank displayed over time

Kushal_Chawda — Wed, 30 Sep 2020 08:54:13 GMT

@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())

Re: Aggr + rank displayed over time

Taoufiq_Zarra — Wed, 30 Sep 2020 08:56:27 GMT

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

Re: Aggr + rank displayed over time

Jonathan_Alm — Wed, 30 Sep 2020 09:06:34 GMT

Thanks, that worked out perfectly!

Kind regards, Jonathan

Re: Aggr + rank displayed over time

Jonathan_Alm — Wed, 30 Sep 2020 11:41:41 GMT

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

Re: Aggr + rank displayed over time

Kushal_Chawda — Wed, 30 Sep 2020 12:09:14 GMT

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

Re: Aggr + rank displayed over time

Jonathan_Alm — Wed, 30 Sep 2020 12:16:14 GMT

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

Re: Aggr + rank displayed over time

sunny_talwar — Wed, 30 Sep 2020 13:34:20 GMT

@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())

Re: Aggr + rank displayed over time

Kushal_Chawda — Wed, 30 Sep 2020 13:43:01 GMT

@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