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.
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...
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?
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:
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]