Last 3 month usage

[#1]

I am trying to to calculate the last 3 months quantity. I have set this up as set analysis as I don't want dates selected from the list box affecting the results.

The expression as it stands is showing the last 2 months and the current month to date. I.e. today it is showing usage from 1^st November to 28^th January.

Sum ({$<YearMonth={">=$(=Date(AddMonths(Max(YearMonth),-2),'YYYY-MMM'))"} >} Quantity/3) 

As I am trying to calculate average monthly usage (based on the last 3 months) I would need the expression to calculate for exactly 3 months. This could be the last 3 complete months (i.e. Oct, Nov & Dec) or 3 months from today’s date.

I appreciate any help that you could provide.

[#2]

Hi James

so you have it

Sum ({$<YearMonth={">=$(=Date(AddMonths(Max(YearMonth),-3),'YYYY-MMM'))  <=$(=Date((Max(YearMonth),'YYYY-MMM'))"} "} >} Quantity) 

gives you 3 months from max(YearMonth) selected

best regards

Chris

[er_mohit]

Try this You Date Field

Sum ({<DateField={'>=$(=num(monthstart(max(Datefield),-3)))<$(=num(Monthend(max(Datefield),-1)))'},YearField=>} Quantity)/3

[jagan]

Hi,

If you have a date field in your data model then try like this

=Sum ({<DateDimensionName={'>=$(=MonthStart(Max(DateDimensionName),-3))<$(=MonthEnd(Max(DateDimensionName),-1))'},YearDimensionName=, MonthDimensionName=>} Quantity)/3

Regards,

Jagan.