How to calculate rolling 3 month with relational data?

[bonniehsueh]

I want to show rolling 3 month two ways. I know it can be done using the period's to date function with olap data.

How can you achieve below results with relational data?

1. Want to display sales for each of the last 3 months like below

Feb  March  May  June (User chosen month)
250  300     50    100

2. Last 3 months (including chosen month) aggregated
Rolling 3 month
700

Thanks in advance for ideas.

[Suraj]

extract month from user's selected date(if directly month is selected, no need) and extract month from date column.
create a filter so that month is between user selected month and user selected month-3...

[bonniehsueh]

Got it. Now I need a column for Sales for the last 12 months in the next column. I will not be able filter at the query level.

How would I get the output for the last 3 months and last 12 months?

Product Line              Rolling 3 month           Last 12 months
Camp Equipment            200                       800

I was told it can be done by creating a query for 3 month, query for 12 month, and unioning them together, however, I wanted to see if there are other ways to do this.

Thoughts?

[blom0344]

You only need 2 seperate case constructs to collect the rolling totals. However, the exact definition may be a bit more complex if you want to transverse over the years. The month dimension is a cyclic one as opposed to the year dimension. You may need to add additional checking if you want a proper calc when the month is january (month = 1)

To give you the example expressed in SQL:

Code Select Expand


SELECT [SOME_DIMENSION],
SUM(CASE WHEN <EXPRESSION1> THEN [SOME_MEASURE] ELSE 0 END) AS MEASURE 1,
SUM(CASE WHEN <EXPRESSION2> THEN [SOME_MEASURE] ELSE 0 END) AS MEASURE 2
FROM
...................................
WHERE
...................................
GROUP BY [SOME_DIMENSION]