Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

qlikviewaf
New Contributor III

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 ?

13 Replies

Re: Accumulation Pivot

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

qlikviewaf
New Contributor III

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?

qlikviewaf
New Contributor III

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?

qlikviewaf
New Contributor III

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

qlikviewaf
New Contributor III

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!

Catturaforum.PNG

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


Capture.PNG

Community Browser