Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
qlikviewaf
Creator
Creator

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
sunny_talwar

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

qlikviewaf
Creator
Creator
Author

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
Creator
Creator
Author

no only on a pivot table

sunny_talwar

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

qlikviewaf
Creator
Creator
Author

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
Creator
Creator
Author

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

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


Capture.PNG