Moving average / sum 30 day period

MS SQL SERVER

    Sponsored Links

    Next

  • 1. Last Year and shifted dates
    Hi All, I'm trying to create LY calc members and while it would calculate properly when it comes to monthly level, daily level presents a problem. It would not be fare to compare, for example, 08/26/2008 to 08/26/2007, as there is a daily seasonality in sales (Tue vs. Sun). Is there a way to build a calc member that would show LY results for 08/28/2007, which represents a Tuesday, one year ago? I can populate a column LY_Date in the date dimension with the corresponding dates, but how would I incorporate that into MDX? Or is there a better way of doing this? -- Thanks, Pasha
  • 2. ProClarity Grand Total MDX
    Hey I have a question regarding ProClarity - Why does it generate MDX for selecting Column Grand Total to the report This should be something which ProClarity should compute on its end rather sending MDX to the server. Also, is there anyway to edit the MDX under MDX editor on ProClarity? Any help is appreciated. Thanks, Manish
  • 3. SSAS Design question
    Hi All, I have a design question . I got the requirement of 2 TB starting for enterprise dataware house in my project. I want to implement next generation warehousing technique. So want to have a near line storage layer in the design. If we seperate data such that current year and last year will be in SAS(Serial Attached SCSI) drive and next past 3 years will be in Near line. E.g. 2007 and 2008 will be in one cube which is located in SAS and 2006,2005 and 2004 will be in another cube stored in near line storage. Can we develop such cubes seperately and how can we combine these for adhoc query option? Another design is have only one cube and use partitions based on years. 2008 & 2007 partition can be stored in SAS drive and remaining on some other drive. Please help me in this. Thanks, RK
  • 4. Monitor Lazy aggregation processing using SQL Server Profiler 2008
    Hi, Anybody knows how to monitor Lazy processing using SQL server profiler? I am using SSAS 2008. I have created cube dimensions and set the ProcessingMode to LazyAggregations. Lazy processing is enabled on the server. When I change the ProcessingMode to Regular and Process a dimension with ProcessUpdate option, Profiler shows Build Indexes etc. When the ProcessingMode is LazyAggregations, I don't see these entries on ProcessUpdate. I am monitoring the profiler to see when AS will build these indexes, but couldn't find it. Is there a way to monitor the same? Thanks, Padmaja

Moving average / sum 30 day period

Postby Aldo Deijkers » Sat, 25 Dec 2004 01:10:51 GMT

Hi,

Currently I'm developing a MDX query which should return the results
(average/sum) of the previous selected 30 days (rolling/moving).

However the problem is that my result set provides me with the days
available (with data) of a particular month.

I.e. October has 31 days, however the result returned from the OLAP
misses October 3, October 19, etc.... I got not 31 cells, but i.e. 27
cells.

I would still like to have the average of this month (meaning the usage
of 31 days). How can I detect the "missing" cells... and how do I know
what "Days" are missing?

Any help much appreciated,

Thanks,
Afd



*** Sent via Developersdex  http://www.**--****.com/  ***
Don't just participate in USENET...get rewarded for it!

Re: Moving average / sum 30 day period

Postby » Sat, 25 Dec 2004 11:09:51 GMT

do you generate your dimension using a table which contain ALL the days of 
the year?
or you generate only the days available in your fact table?
if you are in the second case, then switch to the first one to make sutre 
your dimension always copntain everyday

after, don't use "nonemptycrossjoin" syntax.

do you want a average which is:
Sum of the last 30 days / 30?
(so empty values = 0)

This formula count the number of days without sales in the last 30 days:
Count(filter(LastPeriods(30, closingperiod(Time.Days, Time.Currentmember)), 
isempty(measures.sales)))

This formula return the average sales for the last 30 days (excluding empty 
values)
Avg(LastPeriods(30, closingperiod(Time.Days, Time.Currentmember)),
measures.sales)

This formula return the average sales for the last 30 days  (including empty 
values)
Avg(LastPeriods(30, closingperiod(Time.Days, Time.Currentmember)),
CoalesceEmpty(measures.sales,0))

I hope this can help you.



% XXXX@XXXXX.COM ...



Similar Threads:

1.30 Day Period over Period Moving Average

I'm pretty new to MDX and I'm trying to create a calculated member to
add to my cube based on the following dimension structure:

Year
   Qtr
     Month
       Day (2005-10-03)
         DateHour (2005-10-03 05:00)

What I need to do is create an 30 day moving average by hour of day.
So if the member I'm on is 2005-10-03 05:00, get the 05:00 hour of
every day for the last 30 days and average them, all other hours being
excluded.

Right now I have the following MDX:

Avg
(
   [Session Dt].[Year -  Qtr -  DatePartMonth -  Day -
DateHour].CurrentMember.Lag(30) :
   [Session Dt].[Year -  Qtr -  DatePartMonth -  Day -
DateHour].CurrentMember,
   [Measures].[Not Registered]
)


The problem here is that it just gives me the last 30 hours, I need a
way to have the set being averaged be just the members with the same
hour of day as the member I'm on.

2.Need 30 days worth of average times

we are looking to display average completion time for our 5 page 
application....

since its will be a running 30day average, what would be the best and most 
practical solution....

#1 create a table that will hold only 3 columns and 30days worth of data
Start      End      Average

#2 create a procedure that will check against 2 existing tables to calculate 
this total

----------------------------
Concerns that were brought up..
it would be faster to calculate and display a daily average from a small 
table that only contains 30 days worth of records versus calculating against 
2 different tables that will potentially contain thousands of records in a 
few months....

im new to SQL2005 so its hard to argue the points with the other guys... but 
to me it would make more sense to on start and on end of the application i 
capture the times into a small table that will only contain 30days of 
timestamps...

Anyway.. any suggestions or solutions please let me know.

-- 
Using ASP, SQL2005 and DW8 


3.Query Issue - calculating average for the past 30 days

Hi!
I posted to the wrong group originally.

I have a table called Active1
Columns:

JobID, JobstatusID, activeJobid, StatusDate, Timediff.

Sample Data:

82      867934  289392  2008-05-05 05:50:52.303 +0000-00-00
00:00:07.954
79      867937  289393  2008-05-05 05:55:51.843 +0000-00-00
00:00:00.250
82      867940  289394  2008-05-05 05:55:52.327 +0000-00-00
00:00:07.783
2       867947  289395  2008-05-05 06:00:02.147 +0000-00-00
00:20:37.130

If the same jobID runs more then one (like 82) jobstatusid and
activejobid is different everytime that jobid runs.

I need to be able to get average timediff for each jobid based on the
statusdate (interval 30 days).

Any ideas?
Thank you,
T.

4.Moving average 10 days - working days

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

5.summing one day slower than summing 7 days

This is a strange one that I've been working on all week. I've come to the 
conclusion that summing 1 day is slower than summing 7 days:

MDX1:
with member [Time].[CustomRange] as 
'Sum([Time].[2005].[Jan].[31]:[Time].[2005].[Jan].[31])'
select {[Measures].[MyMeasure]} on columns,
{[Stores].[My Store]} on rows
from MyCube
where ([Time].[CustomRange])

MDX2:
with member [Time].[CustomRange] as 
'Sum([Time].[2005].[Jan].[24]:[Time].[2005].[Jan].[31])'
select {[Measures].[MyMeasure]} on columns,
{[Stores].[My Store]} on rows
from MyCube
where ([Time].[CustomRange])

So MDX1 is summing up just 1 day. MDX2 is summing up 7 days. That's the ONLY 
difference. You'd think that MDX2 would be slower, but it takes half a second 
while MDX takes 5 seconds. I'm getting these statistics from looking at the 
ReportServer.ExecutionLog.TimeDataRetrieval column for a data-driven 
Reporting Services subscription that we run every day. It runs MDX1 for 600 
stores then MDX2 for 600 stores. I thought the performance difference was due 
to caching, so I switched the order... run MDX2 for 600 stores then MDX1 for 
600 stores. MDX2 was still half a second each, and MDX1 was still 5 seconds 
each.

I don't believe it could be aggregations because the Time dimension doesn't 
have a Week level.

I get MDX1 to return in half a second when I change the MDX to:

select {[Measures].[MyMeasure]} on columns,
{[Stores].[My Store]} on rows
from MyCube
where ([Time].[2005].[Jan].[31])

Can anyone explain this? Has anyone else seen this? We're still under AS2000 
SP3a.

6. 10 Day moving average method

7. SQL next 30 days

8. How to query 30 days records from the cube



Return to MS SQL SERVER

 

Who is online

Users browsing this forum: No registered users and 83 guest