Rolling Average 1

[katams2]

I am trying to write an mdx query that calculates the 91-day rolling average of order countm by store, by day of week. Initially, it was supposed to be a 90 day average, but since 91 is divisible by 7 for days of week, I changed it.

The end result should be like:
Store Monday Tuesday Wednesday Thursday...
S-101 56 25 26 42
S-102 45 32 99 63

So, for example, if there Store 101 had 728 total orders on the last 13 Mondays, the average would be 56 for that time period.

Time.Fiscal Dimension:
Time
FiscalYear
FiscalQuarter
FiscalWeekEnding
Day

Day of Week Dimension:
Monday
Tuesday
Wednesday
Thursday
Friday
Saturday
Sunday

Any help would be greatly appreciated!

 

[issahar]

Hi, Katams2!
I have a direction for solutions of your problem,
based on the FoodMart database examle as supported by default olap installation

The query
SELECT {BottomCount({Descendants([TIME],Month)},2,1)} ON COLUMNS,
{Descendants([Store],[Store Name])} ON ROWS
FROM SALES
WHERE [Measures].[Sales Average]

will give you the average sales(using the predefined their calculated member, look inside the foodmart database) results for the last 2 months

I didn't debug this query till the end but it seems me to give you how to solve your problem

I will be very pleasure if you will tell me if my advice helped you.

Issahar

 

[katams2]

issahar,

Thank you so much for your response. I had not used that before. I will let you know how using the BottomCount function works.

I tried this and it works as well:

With Member [Measures].[90DayRollingAverage] as 'avg({([Time].[Fiscal].[FiscalYear].[2001].LastChild.LastChild.LastChild)[Time].[Fiscal].[FiscalYear].[2001].LastChild.LastChild.LastChild.Lag(90)) }, [Measures].[Count])'

Select {([DayofWeek].Children)} on Columns,
{([Sites].Children)} on Rows
From [Orders]
Where [Measures].[90DayRollingAverage]

What do you think of this?

 

[issahar]

Hi, Katams2!
I think your solution is wonderful and it hits to point.
As I mentioned in my message - my proposition was only the direction for solution and not the full solution - to use botton count...

In your solution there is some little problem as I think -
when you have not 90 days back in your time dimension - your mdx query will fail (I am not very sure about that but I suppose this...)

In bottom count - this problem will not exist...
Try to see how to alternate bottom count in your solution...
it may be useful...

Write me what do you think about all that.
If I am wrong - fix me.

Thanks,
Issahar

 

[issahar]

One more thing...
Did you try to use function:
LastPeriods?

 

[katams2]

issahar,

Again, thank you so much for you help! Could you please give me an example of using the lastperiods function? I tried it but can't seem to get it right. All the examples I have read use years, quarters, months, or weeks, but never a definitive day count.

I think if I checked for nulls or empties in what I wrote, it would take care of the problem.

Your thoughts on that?

Thanks!

 

[issahar]

Hi, Katams2

I added the DayOfWeek dimension to the foodmart sample olap database, what do your think about next query?

With Member [Measures].[90DayRollingAverage]
as 'avg({LastPeriods(90,[Time].LastChild.LastChild.LastChild.LastChild.LastChild)}, [Measures].[Sales Count])'
Select {([DayofWeek].Children)} on Columns,
{Descendants([Store],[Store Name])} on Rows
From [Sales]
Where [Measures].[90DayRollingAverage]


this query is your query but I only replaced the use of Lag with using of LastPeriods...

after that I used your query :
With Member [Measures].[90DayRollingAverage]
as 'avg({[Time].LastChild.LastChild.LastChild.LastChild.LastChild:[Time].LastChild.LastChild.LastChild.LastChild.LastChild.Lag(90)}, [Measures].[Sales Count])'
Select {([DayofWeek].Children)} on Columns,
{Descendants([Store],[Store Name])} on Rows
From [Sales]
Where [Measures].[90DayRollingAverage]

The results are entirely different?!!??!!??!!

SO, it is interesting for me to know what is wrong in my query...

--------------------------------------------------
I think that the right way maybe the next query:
Select NON EMPTY Generate(LastPeriods(90,[Time].LastChild.LastChild.LastChild.LastChild.LastChild),[DayofWeek].Children)
On columns,
NON EMPTY Descendants([Store],[Store Name]) on rows
from Sales
WHERE [Measures].[Sales Average]

because I saw the database and the data seems to be exact for the first sight
to the right returned data...
All the above queries except the last one return entirely wrong data...

All this in my case, because I really don't know how is your database is designed

Issahar
I will be very pleasure to know you opinion about all this,,,