asked on

12 Month Moving Average. Do I create it in SQL or in my Report Writer

Hi,
I have a SQL Database with a table called DRSTRAN. With it, I have used Analysis Services to create a cube that contains all the information I need so that we can analyse the spend per month per customer
eg
Cust 1
Jan 1000
Feb 1500
Mar 750
etc
From our BI Tool (Zap) we created a cross tab report that gives the above scenario. Now the boss wants to see a moving average based the last 12 months.

My question is:
1. I created a view first, do I create the formula in the view section
2. From the view I created the cube, Do I create the formula at this level
3. From the cube I create the pivot table, do I create the formula at this level,

And the rub, how what is the formula syntax ?

Thanks again for getting me out of a jam

Larry

debuggerau

I would use the BI Tool to do the moving average on, since you already have the monthly spend per customer, its just a matter of working out the moving average, trouble would be that for an accurate 10 month moving average, you will need 10 months of history before it was an effective value...
Maybe making the report over 2 years could suffice.

logonlarry

ASKER

Thanks for quick reply.
I have in fact over 5 years of history but the boss only wants the last 12months (something I don't agree with). How do I create a formula on this data where the value field is called Net_Value.

Larry

PFrog

I would create it within your cube, you can then use this figure in any report without having to repeat any complex calculations.

Add something like this into your calculated members

CREATE MEMBER CURRENTCUBE.[MEASURES].[Net_Sales_Rol_Avg]
AS AVG( LastPeriods(12,[Time].CurrentMember), [Measures].[Net_Value] ),
FORMAT_STRING = "Currency",
VISIBLE = 1 ;

You can then just bring out two measures in your report, Net_Sales and Net_Sales_Rol_Avg.

This will also show the last 12 periods, whatever they may be. If you change your MDX to bring out weeks, it will show you a rolling average of 12 weeks.

logonlarry

ASKER

PFrog,
Thankyou and I think you are on the right track. However, when I try to create a calculated member using your code, I get errors, and I think that I'm inserting your code in the wrong place. I am using SQL 2000. Sorry for being so dumb. Where do I go to create this member

Larry

ASKER CERTIFIED SOLUTION

PFrog

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

logonlarry

ASKER

Hey PFrog,

Thanks again.

Here is my snippet as per your instructions
AVG( LastPeriods(12,[Date].[Month].CurrentMember),[Measures].[Members Purchases] )

When I check the syntax I get the following response

Analysis Manager:
'Unable to update the calculated member.
Formula error - cannot bind: unknown dimension: "[Date.[Month]"

Date: is a Time based Dimension with Year, Month

The formula only allows me to use [Date]
Hope you can help

Larry

PFrog

The error message indicates that you've missed the "]" off "[Date]"

The syntax is definately correct - can you check your spelling...?

logonlarry

ASKER

Success! It turned out that I had a field (another date field) in my row that decribed date and year and that was upsetting the report. Using just [Date] works a treat.

Thanks and love your Blogs.
Larry

logonlarry

ASKER

Success !
using just [Date] works correcltly. It was my design in the report that was wrong.

Thanks for your patience.

Larry