Aggr() function with ranking in a line chart

Will1 — Tue, 28 Sep 2021 16:38:33 GMT

Hi everyone - HELP! I am trying to create something similar to the line chart below created in excel. This contains a line for average sales for each year by year for the top 2 area rankings across 3 years.

The rolled up data below is the sum of sales for all 3 years. So Areas A & E should be included in the line chart.

Area	Sales	Rank
A	2,948,491.20	2
B	2,038,385.95	4
C	2,023,691.53	5
D	2,735,826.86	3
E	3,364,687.01	1

I want to be able to include lines on the chart for the top 2 rankings. In reality, there are a lot more Areas, but I have simplified the data.

The aggregated data that needs to be included in the line chart for the top 2 rankings is below. In reality, there are 4 years, but I have simplified the data.

I started with this -

Aggr(Sum(Sales)/count({$ <SalesFlag={"Yes"}>}SalesFlag),Year)

Count is based on SalesFlag being "Yes". "No"'s are ignored.

I have not figured out how to add Area into the AGGR() function. I keep getting nothing back.

Area	Year	Sales	Count	Average Sale
A	2018	686,149.99	40	17,153.75
A	2019	1,388,791.92	40	34,719.80
A	2020	873,549.29	40	21,838.73
B	2018	876,765.22	21	41,750.72
B	2019	665,434.10	21	31,687.34
B	2020	496,186.63	21	23,627.93
C	2018	197,548.43	11	17,958.95
C	2019	518,430.40	11	47,130.04
C	2020	1,307,712.70	11	118,882.97
D	2018	976,574.69	37	26,393.91
D	2019	646,155.98	37	17,463.68
D	2020	1,113,096.19	37	30,083.68
E	2018	922,409.02	51	18,086.45
E	2019	2,058,917.69	51	40,370.94
E	2020	383,360.30	51	7,516.87

The data attached has had the No sales removed for all years and areas.

Should I be using the aggr() function? if not, what should I use?

Thank you for any help you can offer.

topic Aggr() function with ranking in a line chart in App Development

Aggr() function with ranking in a line chart