topic Re: Rolling 12 months display in QlikView

Rolling 12 months display

Sandy10 — Mon, 07 Nov 2022 06:19:46 GMT

I am new to Qlkivew desktop app. I am trying to create bar chart with line as %.2 Queries

1. I want to create a month/year filter. I need to use this to display last 12 months on the bar chart. Attached the bar chart. (if is select sep22, then it shld display from Sep-21 to Sep22)

Excel file contains following fields. Sample data

Date Product Country Sales %

Jan-21 IN CG 20000 23%

Feb-21 IN CG 23000 80%

Mar-21 IN CG 23000 80%

....

Sep-22 IN CG 23000 80%

Jan-21 TT KL 20000 23%
Feb-21 TT KL 20000 23%
Mar-21 TT KL 20000 23%
....
Sep-22 TT KL 20000 23%

Kindlya dvise how to do rolling months. Thanks!

Re: Rolling 12 months display

Sandy10 — Mon, 07 Nov 2022 07:04:47 GMT

Can someone reply pls ? 😞 Am stuck on how to do. I dont have qliksense. Only qlikview desktop app.

Re: Rolling 12 months display

rubenmarin — Mon, 07 Nov 2022 07:26:52 GMT

Hi, you can try with set analysis like:

Sum({<Date={">$(=AddMonths(Max(Date),-13))<$(=Max(Date))"}>} Value)

And if you need accumulation check the option to modify -> Accumulation in the measure.

Date may give isssues with the formats, maybe needs a fix to make it work with your data.

If you really need a rolling12M (meanin that each month needs access to the previous 12) post the expression you are using, or check the as-of table: https://community.qlik.com/t5/Design/The-As-Of-Table/ba-p/1466130

Re: Rolling 12 months display

Sandy10 — Mon, 07 Nov 2022 07:41:08 GMT

Hi @rubenmarin

Thanks for your reply. I dont have Qliksense. Is it there in Qlikview desktop app?

Re: Rolling 12 months display

rubenmarin — Mon, 07 Nov 2022 07:48:24 GMT

Hi, in qlikview there is also an option to accumulate in the expressions tab, and the analysis and script works alike in the two products.

Re: Rolling 12 months display

Sandy10 — Mon, 07 Nov 2022 09:31:05 GMT

Hi @rubenmarin . I dont want any rolling avg or total. Just that if I select sep'22 it should display the bar char from Sep-21 to Sep22. how to create this month first of all toselect?

Re: Rolling 12 months display

rubenmarin — Mon, 07 Nov 2022 09:33:22 GMT

Hi, just use the set analysis I posted in my first answer.