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?
