Monthly Avearage Calculation

[#1]

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

[johnw]

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.