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.
