topic Re: Rolling N Months in QlikView

Rolling N Months

nareshthavidishetty — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

We have logic as sum(sales).

User needs to see the data like roll back to last 12 months.

Is there any way to get this.

Thanks..

Re: Rolling N Months

karthikoffi27se — Mon, 22 Jan 2018 11:59:34 GMT

Hi Naresh,

Please refer the below thread for rolling 12 months analysis

Rolling 12 Month Sales using Set Analysis Chart Sort?

Many Thanks

Karthik

Re: Rolling N Months

Ben_P — Mon, 22 Jan 2018 15:54:15 GMT

When you are loading your calendar, include rowno() as a field to load (e.g. rowno() as MonthSequence), and make sure that you are loading your years and months in ascending order. This will then give you a numbered sequence from month to month, and it also solves the year-end problem where e.g. Dec 2017 precedes Jan 2018.

You can then perform calculations on the MonthSequence field

Re: Rolling N Months

nareshthavidishetty — Tue, 23 Jan 2018 12:21:55 GMT

Hi,

When ever user select a month it should rollback to last 3 months.

Ex: If the user select year =2018 and month = Jan ,the expected results would be sum of orders roll back to 3 months like sum(Jan + Dec + Nov orders)

Thanks..

Re: Rolling N Months

Ben_P — Tue, 23 Jan 2018 12:35:02 GMT

Yes, you can do this by loading your month and year data (sorted ascending) into a table, and loading rowno() as well.

This would give results as below, for example:

rowno() yearmonth

1 201709

2 201710

3 201711

4 201712

5 201801

6 201802

Then you can use -3 against your rowno() field to count back 3 months, regardless of whether or not it wraps around to a previous year.

Re: Rolling N Months

nareshthavidishetty — Tue, 23 Jan 2018 12:39:51 GMT

Hi Ben,

Thanks for the post.

Is it is possible in set analysis.Actually we have used a text object to display the orders.

So based on month and year selection the roll back will happen on max(Month).

Thanks..

Re: Rolling N Months

Ben_P — Tue, 23 Jan 2018 12:47:43 GMT

Something like this should work, where 'MonthSequence' is your rowno() field:

Sum({<MonthSequence = {">=max(MonthSequence)-3"}> } Sales)