How can I show an all-years average by week on a line chart showing revenue by year by week?
Scenario:
Platform: Qlik Sense
Chart style: Line chart
Dimensions: "Week Number in Year" and "Year"
Measure: Sum(Revenue)
X axis: "Week Number in Year" (1-53)
Y axis: Sum(Revenue), one line for each year (2014 revenue by week, 2015 revenue by week, 2016 revenue by week, etc.)
Requirements:
Include an average revenue line for all displayed years for each week
If there are 3 years displayed, I will see 4 lines: one for each year, and one for the average of the 3 displayed years for each week number along the X axis
The average revenue line can (should?) be a Reference Line Add-In, but it must not be a straight line; it will vary by week number as the average revenue by week across all years varies
Ideally, if the user filters on Year (e.g., selects just 2015 and 2016), the average revenue across "all" years will respect the user's selection (e.g., average of 2015 and 2016, ignoring 2014)
My chart currently shows the revenue by year and week number, but I'm looking for guidance on how to create the average reference line. I've tried various approaches, using Set Analysis and Aggr, but I haven't been able to make it work. This seems like a basic/common use case. Can anyone help me?
