MS SQL Server OLAP - Moving average 10 days

Author

Moving average 10 days - working days

jay_homsys

2006-03-28, 1:31 pm

Hi,

i have my time dimension as follows :

idtime, year, month, week, fulldate, working_day( true or false)

my fact table is sales which contain fact SalesinEuro
i would like to do a moving average for a period of ten working days
....

avg([time].[fulldate].currentmember.lag(10) :
([time].[fulldate].currentmember, [measures].[SalesinEuro])

the lag function give set of dates which are not working days and i
would like to obtain the lagging for working days only ..excluding
saturdays and sundays.
Working_day in my time dimension table tells wether the date
corresponds to a working day or not ( true or false)

Please help me guys

Deepak Puri

2006-03-28, 8:27 pm

Assuming that there is a [working_day] Attribute Hierarchy:
[color=darkred]
Tail(Exists({NULL:[time].[fulldate].currentmember},
{[working_day].[true]}), 10)[color=darkred]

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.droptable.com ***

jay_homsys

2006-03-30, 8:27 pm

hi Deepak,

thks it was of great help but now that i ve got my moving average
taking only working days the problem i am having is that on the 10th of
february my moving average gives the average 10 last days but on the 9
th february my moving average gives the average of the last 9 days only
and on the 1 st of january it does not go to fetch the avg of the last
10 days in the month of january.

To be clearer,
date Sales MA Sales by 10 days
feb 01 380000 380000 /* for example here Deepak you see that it
does not move to feb.
feb 02 100000 240000
feb 03 110000
feb 04 115500
.........
feb 10 100000

Can you give me an idea how to solve this problem Deepak ? thks

Deepak Puri

2006-03-31, 3:31 am

Not sure how your date hierarchy is structured, so here's an example
from Adventure Works of a Sales 10-working-day moving average, using the
[Date].[Calendar] hierarchy (you can compare its design to that of your
date heirarchy).

Since there is no separate [WorkDay] attribute, I grouped 5 days
(Monday-Friday) into a set instead:
[color=darkred]
With
Member [Measures].[DayOfWeek] as
[Date].[Calendar].Properties("Day Name")
Member [Measures].[Sales10DayMA] as
Avg(Tail(Exists(NULL
:[Date].[Calendar].CurrentMember,
{[Date].[Day Name].[Monday]
:[Date].[Day Name].[Friday]}), 10),
[Measures].[Sales Amount])

select {[Measures].[DayOfWeek],
[Measures].[Sales Amount],
[Measures].[Sales10DayMA]} on 0,
{[Date].[Calendar].[Date].&[565]
:[Date].[Calendar].[Date].&[590]} on 1
from [Adventure Works]
----------------------------------------------------
DayOfWeek Sales Amount Sales10DayMA
January 16, 2003 Thursday $20,05
7.28 $15,119.53
January 17, 2003 Friday $11,047.22 $14,872.29
January 18, 2003 Saturday $12,97
2.21 $14,872.29
January 19, 2003 Sunday $9,418.28 $14,872.29
January 20, 2003 Monday $15,176.83 $15,953.66
January 21, 2003 Tuesday $12,316
.17 $15,722.96
January 22, 2003 Wednesday $16,6
08.75 $15,810.51
January 23, 2003 Thursday $21,35
7.59 $16,132.10
January 24, 2003 Friday $13,759.08 $15,952.18
January 25, 2003 Saturday $6,298
.20 $15,952.18
January 26, 2003 Sunday $20,300.63 $15,952.18
January 27, 2003 Monday $5,275.44 $14,393.55
January 28, 2003 Tuesday $11,450
.57 $14,304.68
January 29, 2003 Wednesday $13,4
74.23 $14,052.32
January 30, 2003 Thursday $9,240
.43 $12,970.63
January 31, 2003 Friday $17,468.54 $13,612.76
February 1, 2003 Saturday $2,402
,984.40 $13,612.76
February 2, 2003 Sunday $19,623.90 $13,612.76
February 3, 2003 Monday $11,302.45 $13,225.32
February 4, 2003 Tuesday $30,851
.06 $15,078.81
February 5, 2003 Wednesday $16,8
34.82 $15,101.42
February 6, 2003 Thursday $22,60
1.37 $15,225.80
February 7, 2003 Friday $15,937.81 $15,443.67
February 8, 2003 Saturday $17,21
5.79 $15,443.67
February 9, 2003 Sunday $20,491.40 $15,443.67
February 10, 2003 Monday $14,937.37 $16,409.86[color=darkred]

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.droptable.com ***

Sponsored Links