How to show sum for previous 3 months in one coulmn and sum of corresponding month in another column of a straight table?

[medempudi_ravi]

Hi Community,

I have a situation where I need to create a straight table where,

• Dimension is Month End field where I need to show Trailing 10 months
• In first expression, I am showing sum(Field X)  which shows the corresponding sum grouped by MonthEnd.

In the next column of the table, I Need to show Sum(Field X) for Previous 3 Month Ends. Can you help me write the expression for this column.

Please find the attached excel which shows example of my requirement.

Thanks in Advance.

[MK_QSL]

Try to make yourself reading below link... Very useful...

Calculating rolling n-period totals, averages or other aggregations

[#3]

Hello Manish, the link that you have shared is for Rolling n-periods, and not trailing. And what I have understood from the problem statement defined by Raviteja is that, he doesn't want a cumulative total for last 3 months, rather I think he wants something like below for which even I am looking for a solution:

Say our data looks like this:

Month_Period                    Amount

Jan 2012                              100

Feb 2012                              200

Mar 2012                              150

Apr 2012                               50

May 2012                              100

Jun 2012                                50

and so our trailing 3 months graph or chart should look something like this:

Month_Period                    Trailing_Amount

Jan 2012                              100 [=100+0+0, 0's from previous months]

Feb 2012                              300 [=200+100+0]

Mar 2012                              450 [=150+200+100]

Apr 2012                              400 [=50+150+200]

May 2012                             300 [=100+50+150]

Jun 2012                               200 [=50+100+50]

Please share if you have made any progress on this problem statement.

Thanks.

[medempudi_ravi · Accepted solution]

Hi Bikash,

I could successfully solve the problem I have defined using the formula similar to one below:

sum( aggr( rangesum( above( sum(Sales),0,12) ),Month))

Hope this is helpful for you:)

Thanks