Rolling 12 months based on user date selection

[Anonymous]

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?

[OmarBenSalem]

Do u have dates fields? date? Month?

[Anonymous]

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)

[Anonymous]

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

[Anonymous]

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)

[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

[Anonymous]

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

[OmarBenSalem]

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