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?

[Webinar] Streamline your web hosting managementRegister Today

PFrogConnect With a Mentor Commented:
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.
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.

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

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.
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

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.

All Courses

From novice to tech pro — start learning today.