Rolling N Months

[nareshthavidishetty]

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..

[karthikoffi27se]

Hi Naresh,

Please refer the below thread for  rolling 12 months analysis

Rolling 12 Month Sales using Set Analysis Chart Sort?

Many Thanks

Karthik

[ben_pugh]

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

[nareshthavidishetty]

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..

[ben_pugh]

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.

[nareshthavidishetty]

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..

[ben_pugh]

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

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