Accumulation Pivot

[qlikviewaf]

I would like to have the sum of last 12 month based on the maximum "Invoice_date" done on the selection.

How this can be build?

This is the formula for sales based on the selection (imagine May - 2017)

=Sum(ExtendedUSD)

Which shuold be the formula to retrieve the last 12 month sales ?

[qlikviewaf]

THanks Sunny.

What i have to change on the formula to have rolling 12 month instead of 3 ?

THanks!

[sunny_talwar]

Change the 2s to 11s and 3 to 12. But, remember you are using Month as your dimension and you will run into the issue of not having Year with them... is there any way you can use MonthYear instead of Month as your dimension?

=RangeSum(Before(if(vExcludeInstrumentSales,

Sum({<MonthYear = {"$(='>=' & Date(AddMonths(Max(MonthYear), -2), 'MMM-YYYY') & '<=' & Date(Max(MonthYear), 'MMM-YYYY'))"}, Month, Year>}ExtendedUSD),

Sum({<Item_Category={$(vNoInstruments)}, MonthYear = {"$(='>=' & Date(AddMonths(Max(MonthYear), -2), 'MMM-YYYY') & '<=' & Date(Max(MonthYear), 'MMM-YYYY'))"}, Month, Year>}ExtendedUSD)), 0, 3))

[qlikviewaf]

I have MOnth-Year as dimension so how this can be switched? (the dimension MOnth-year is linked to the Month as is in the Calendar)

[sunny_talwar]

Cool, just use MonthYear instead of Month then