13 Replies Latest reply: Dec 21, 2017 3:31 PM by Sunny Talwar

# Accumulation Pivot

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 ?

• ###### Re: Accumulation Pivot

Do you have MonthYear field as a dimension? Is it pivoted? Essentially going down or going across?

• ###### Re: Accumulation Pivot

Yes i have it. MonthYear is on my data model.

If Jan17 is selected, i would like to have sum (Jan 16 -> Dec 16 )

• ###### Re: Accumulation Pivot

Is it in the chart also?

• ###### Re: Accumulation Pivot

no only on a pivot table

• ###### Re: Accumulation Pivot

That is what I meant... do you mind sharing a sample to help you better?

• ###### Re: Accumulation Pivot

Find attached.

Let's say that i would like to have rolling of previous 3 month based on the month selected (.e.g November 2017)

Thansk

• ###### Re: Accumulation Pivot

Try this expression

=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))

• ###### Re: Accumulation Pivot

it works!

btw, as you can see below, if i select MONTH as dimension on top, the rolling is then under each month.

There's a way to have Sales as value under Month, and then on the right just one column with the Rolling Sales ?

THanks!

• ###### Re: Accumulation Pivot

Sales

=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))

Rolling Sales 12M

=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))

• ###### Re: Accumulation Pivot

THanks Sunny.

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

THanks!

• ###### Re: Accumulation Pivot

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))

• ###### Re: Accumulation Pivot

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)

• ###### Re: Accumulation Pivot

Cool, just use MonthYear instead of Month then