Skip to main content
Announcements
Qlik Connect 2024! Seize endless possibilities! LEARN MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Monthly Avearage Calculation

Hi ,

I want to calculate average monthly sales figures for last 12 months. User can select select any Year, Month and the calculation should show average sales for last 12 months.

Any help will be greatly appreciated

1 Reply
johnw
Champion III
Champion III

Assuming that Year and Month are part of a calendar that includes Date, and that you have version 8.5 or above, something like this:

avg(aggr(sum({<Year=,Month=,Date={">=$(=date(addmonths(monthstart(max(Date)),-11)) <=$(=date(monthend(max(Date))))"}>} Sales),Month))

We're using set analysis here. The Date={...} part of the expression says we want to include all dates in the maximum selected month (maximum of all months if none selected) and in the eleven months prior to that. Then we say "Year=,Month=" to tell it to then ignore those selections, since they may conflict with the date range we just asked for. You'd want to do this for every field in your calendar except date. The aggr(sum(... Sales),Month) says we want to sum up the Sales by Month. Finally, avg() gives us the average of these monthly sales, assuming I did everything correctly.