April 12, 2015 at 9:45 pm
I'm starting to learn DAX (which is kinda cool!)... but I was wondering about how to do something in T-SQL...
Say I have a really simple data warehouse like ContosoDW. I'm interested in the Date dimension and then SalesFact. I want to determine a rolling average sales per week over a year-wide window. I saw Dwain's article[/url] discussing it, but maybe it went over my head. Is doing the rolling 12 month average as simple as this minor tweak?
SELECT [Date], Value
,Rolling12Months=
(Value +
LAG(Value, 1) OVER (ORDER BY [Date]) +
LAG(Value, 2) OVER (ORDER BY [Date]) +
LAG(Value, 3) OVER (ORDER BY [Date]) +
LAG(Value, 4) OVER (ORDER BY [Date]) +
LAG(Value, 5) OVER (ORDER BY [Date]) +
LAG(Value, 6) OVER (ORDER BY [Date]) +
LAG(Value, 7) OVER (ORDER BY [Date]) +
LAG(Value, 8) OVER (ORDER BY [Date]) +
LAG(Value, 9) OVER (ORDER BY [Date]) +
LAG(Value, 10) OVER (ORDER BY [Date]) +
LAG(Value, 11) OVER (ORDER BY [Date])
)/12
FROM #RollingTotalsExample;
Of course, if I'm completely off the mark, point me at some good reading. Checked Itzik Ben-Gan's Windowing functions book, but the answer was nowhere to be found... any good reading out there on the topic?
Thanks!
Pieter
April 13, 2015 at 1:45 am
As long as there are no holes in the date sequence (i.e. no months missing), the T-SQL code seems correct.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 13, 2015 at 7:51 am
Or you could do something like this
DECLARE @Table TABLE
(
Value int
,Monthly smallint
)
INSERT INTO @Table
(Value,Monthly)
VALUES
(1,1)
,(2,2)
,(3,3)
,(4,4)
,(5,5)
,(6,6)
,(7,7)
,(8,8)
,(9,9)
,(10,10)
,(11,11)
,(12,12)
,(13,13)
,(14,14)
SELECT
*
,SUM(Value)
OVER (ORDER BY Monthly
ROWS BETWEEN 11 PRECEDING AND Current Row) RollingTotal
,SUM(Value)
OVER (ORDER BY Monthly
ROWS BETWEEN 11 PRECEDING AND Current Row)/12.00 RollingAverage
FROM
@Table
The catch is that there cannot be any gaps in the data.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
April 13, 2015 at 12:02 pm
Okay, I was going to use a Calendar table to force the dates to exist. DAX is just still kind of mind-boggling.
I figured out the solution after the helpful push in the right direction - the last one seems to work:
use tempdb;
go
DECLARE @Table TABLE
(
Value int
,Monthly smallint
)
INSERT INTO @Table
(Value,Monthly)
VALUES
(1,1)
,(2,2)
,(3,3)
,(4,4)
,(5,5)
,(6,6)
,(7,7)
,(8,8)
,(9,9)
,(10,10)
,(11,11)
,(12,12)
,(13,13)
,(14,14)
SELECT
*
,SUM(Value)
OVER (ORDER BY Monthly
ROWS BETWEEN 11 PRECEDING AND Current Row) RollingTotal
,SUM(Value)
OVER (ORDER BY Monthly
ROWS BETWEEN 11 PRECEDING AND Current Row)/12.00 RollingAverage
,SUM(Value)
OVER (ORDER BY Monthly
ROWS BETWEEN 11 PRECEDING AND CURRENT ROW)/(1.00*Value) AS Rolling2
FROM
@Table
Thanks!
April 14, 2015 at 5:45 am
No Problem, I've been doing a lot with the new window functions especially the ranges for Running totals.
I've also looked at DAX and found its missing some of the useful functions that exist in MDX, such as PeriodsToDate, ParallelPeriod etc, which make rolling averages so much easier.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply