Bullet Chart Segments, Average Sales by Month

[MTS95]

Hi there,

I have a bullet chart that showcases total revenue broken down by month, using data from the last 11 years. When no year is selected from the filter pane, it defaults to showing the total revenue accumulated each month, like so:

When a year is selected from the filter pane, a few things happen:

1) the black target line shows how much revenue was generated in that month for the previous year,

2) the colored segments show what 75% of last year's revenue was (light red) and what 125% of last year's revenue was (light green), and

3) the bar changes from green to red if this year's revenue is smaller than last year's revenue.

Below, I have selected the year 2018, and the target line and segments show that information from 2017:

Having the segments show 75% and 125% of last year's sales is fine, but I think it would be more interesting to have the segments show the average total revenue of that month from all the years. That way, I could show how different or how similar the selected year's total revenue is from the usual revenue made that month.

I'm using this expression to create a segment to show the average:

=avg(aggr(sum([sales.TotalRevenue]),sales.Month))

but Qlik returns with the segments lining up perfectly with the selected year's total revenue:

How can I adjust my expression so that Qlik aggregates data from each month and finds the average revenue from each January, each February, each March, etc.? If I can get that working, I'd love to add more segments to show how many standard deviations away from the average the selected year's revenue is, so any advice for that would be greatly appreciated as well.

Let me know if more information is required. Thanks in advance!