Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save $400 before Dec 31st: REGISTER NOW!
cancel
Showing results for 
Search instead for 
Did you mean: 
ext_bay
Employee
Employee

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

Do u have dates fields? date? Month?

ext_bay
Employee
Employee
Author

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

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

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

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

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

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

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

OmarBenSalem

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