Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

ext_bay
New Contributor III

Rolling 12 months based on user date selection

Hi all,

I am trying to create a linechart. Based on the date that the user chooses, I need to display the current rolling 12 months and previous rolling 12 months. This also means I need a dynamic x-axis.

Right now I only have

Rangeavg(Above(sum(Sales), 0, 12))

Which should be the current rolling 12 months for the sum of sales.

Any ideas?

12 Replies
omarbensalem
Esteemed Contributor

Re: Rolling 12 months based on user date selection

Do u have dates fields? date? Month?

ext_bay
New Contributor III

Re: Rolling 12 months based on user date selection

Yes I have dates and months. I only want to display 12 months though, but these will be determined on the selection that the user makes.

omarbensalem
Esteemed Contributor

Re: Rolling 12 months based on user date selection

alter ur expression as follow (dpends on the name of ur fields=

sum({<DATE=, Month=,Year=,YearMonth=,DATE={">=$(=addMonths(max(DATE),-12)) <= $(=max(DATE))"}>}Sales)

ext_bay
New Contributor III

Re: Rolling 12 months based on user date selection

Ok thank you! And for the previous 12 months rolling, do I just change '-12' to '-24'? Right now I have 'Months' as a dimension, but if I select a date in June for example, I want June to be the 12th month in the graph. Do you know if this is possible?

omarbensalem
Esteemed Contributor

Re: Rolling 12 months based on user date selection

Sure; it depends on weither u write < or <=; if it's <=, it will include the selected month.

Try to adapt it to ur fields and tell me if it worked.

You can always refer to this thread, in which we discussed different ways of acheiving this (depedns on the demeand/ data model)

Rolling sum on Qlik Sense

and this thread in which I explained step by step some time expressiosn that could help u understand the full logic of them :

YTD, MTD issue

ext_bay
New Contributor III

Re: Rolling 12 months based on user date selection

It doesn't do exactly what I want it to. As you can see even though I make the selection on a date in November, the 12th month is still December.

Rolling 12 Months CY: sum({<OrderDate=, [Fiscal Month]=,Year=,MonthYear=,OrderDate={">=$(=addMonths(max(OrderDate),-12)) <= $(=max(OrderDate))"}>}Sales)

Rolling 12 Months LY: sum({<OrderDate=, [Fiscal Month]=,Year=,MonthYear=,OrderDate={">=$(=addMonths(max(OrderDate),-24)) <= $(=max(OrderDate))"}>}Sales)

Screenshot.PNG

omarbensalem
Esteemed Contributor

Re: Rolling 12 months based on user date selection

Don't use Month as dimension, u have to also include the Year;  because when u select November and going back 12 month, u are in december of the previous year,; this is why u're having december..

alter ur dimension from Months to MonthYear (if u have such a field)

or simply Month&'/'&Year

ext_bay
New Contributor III

Re: Rolling 12 months based on user date selection

With the same selection, it know looks like this..Screenshot2.PNG

omarbensalem
Esteemed Contributor

Re: Rolling 12 months based on user date selection

can u share a sample app please, with just this chart?