topic Re: Accumulation Pivot in QlikView

Accumulation Pivot

qlikviewaf — Thu, 21 Dec 2017 16:23:21 GMT

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

sunny_talwar — Thu, 21 Dec 2017 16:30:39 GMT

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

Re: Accumulation Pivot

qlikviewaf — Thu, 21 Dec 2017 16:56:50 GMT

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

sunny_talwar — Thu, 21 Dec 2017 17:02:05 GMT

Is it in the chart also?

Re: Accumulation Pivot

qlikviewaf — Thu, 21 Dec 2017 17:03:37 GMT

no only on a pivot table

Re: Accumulation Pivot

sunny_talwar — Thu, 21 Dec 2017 17:07:19 GMT

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

Re: Accumulation Pivot

qlikviewaf — Thu, 21 Dec 2017 17:22:31 GMT

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

sunny_talwar — Thu, 21 Dec 2017 17:55:08 GMT

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

qlikviewaf — Thu, 21 Dec 2017 19:39:50 GMT

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

sunny_talwar — Thu, 21 Dec 2017 19:44:08 GMT

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

qlikviewaf — Thu, 21 Dec 2017 20:00:56 GMT

THanks Sunny.

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

THanks!

Re: Accumulation Pivot

sunny_talwar — Thu, 21 Dec 2017 20:04:52 GMT

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

qlikviewaf — Thu, 21 Dec 2017 20:29:09 GMT

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

sunny_talwar — Thu, 21 Dec 2017 20:31:57 GMT

Cool, just use MonthYear instead of Month then