Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
juriengroot
Not applicable

Set analysis, 12 months prior with p() function

Hi,

I have a table with the following column / measure:

Sum({$<[Order Ship MonthID]= p(MonthID), [Order Create MonthID]= p(MonthID)   >}Orders)

That is working perfect for what I want. Now I want to have the values from 12 months prior next to it.

I tried something like:

Sum({$<[Order Ship MonthID]= p($(MonthID)-12), [Order Create MonthID]= p($(MonthID)-12)  >}Orders)

Is it possible to have a calculation for all values in p()? What would be the syntax?


Thanks in advance!

3 Replies
sunny_talwar
Not applicable

Re: Set analysis, 12 months prior with p() function

May be create a new field in the script

LOAD MonthID,

          AddMonths(MonthID, -12) as PrevMonthID

and then try this

Sum({$<[Order Ship MonthID]= p(PrevMonthID), [Order Create MonthID]= p(PrevMonthID)>}Orders)

juriengroot
Not applicable

Re: Set analysis, 12 months prior with p() function

Hi Sunny, Good idea, I tried it but it plots the values not on the same line in that case. It just brings in the period of 12 months prior. I guess this makes sense, but I'd like to Jan 2017 with the values of Jan 2016 next to it. Any idea?

orders.PNG

I would like to keep the months continuous and not discrete!

sunny_talwar
Not applicable

Re: Set analysis, 12 months prior with p() function

There is few ways you can handle this

1) Use The As-Of Table‌ or

2) Use Missing Manual - Above() and Below()