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

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
Cust 1  
Jan    1000
Feb    1500
Mar     750
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

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.
logonlarryAuthor Commented:
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.

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

 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.
Powerful Yet Easy-to-Use Network Monitoring

Identify excessive bandwidth utilization or unexpected application traffic with SolarWinds Bandwidth Analyzer Pack.

logonlarryAuthor Commented:
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

In the tree view of the cube editor, right click on the Calculated Members folder and select "New Calculated Member..."

Change the Member name to Net_Sales_Rol_Avg
Put this in the value expression
    AVG( LastPeriods(12,[Time].CurrentMember), [Measures].[Net_Value] )

Note that you will need to replace "[Time]." with the appropriate time dimension (and possibly hierarchy) in your cube.

Shout if you need more info.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
logonlarryAuthor Commented:
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


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

The syntax is definately correct - can you check your spelling...?
logonlarryAuthor Commented:
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.
logonlarryAuthor Commented:
Success !
using just [Date] works correcltly. It was my design in the report that was wrong.

Thanks for your patience.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.