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 ?

[sunny_talwar]

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

[qlikviewaf]

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

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

[sunny_talwar]

Is it in the chart also?

[qlikviewaf]

no only on a pivot table

[sunny_talwar]

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

[qlikviewaf]

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

[sunny_talwar]

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

[qlikviewaf]

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!

[sunny_talwar]

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