Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 1st November to 28th 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.
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
Try this You Date Field
Sum ({<DateField={'>=$(=num(monthstart(max(Datefield),-3)))<$(=num(Monthend(max(Datefield),-1)))'},YearField=>} Quantity)/3
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.