September 12, 2016 at 11:36 am
I know folks have asked this before, but none of the answers I've seen address my particular problem. I have a cube based on the AdventureWorksDW database, reseller sales. I am trying to get the moving average and I have the following in my script:
CALCULATE;
CREATE MEMBER CURRENTCUBE.[Measures].[Reseller Sales Moving Average]
AS Avg
(
[Date].[Calendar].CurrentMember.Lag(6):
[Date].[Calendar].CurrentMember,
[Measures].[Reseller Sales Amount]
) // This calculation returns the average value of a member over the specified time interval.
,
FORMAT_STRING = Standard,
NON_EMPTY_BEHAVIOR = { [Reseller Sales Amount] },
VISIBLE = 1 , ASSOCIATED_MEASURE_GROUP = 'Fact Reseller Sales' ;
I checked the syntax and it appears to be correct.
Now when I run a query in SSMS, nothing comes back.
SELECT {Measures.[Reseller Sales Amount], Measures.[Reseller Sales Moving Average]} ON 0,
NONEMPTY([Date].[Calendar].[Month].Members) ON 1
FROM [Reseller Sales] -- reseller sales is the name of my cube
Grateful for any insight on my ridiculously simple problem.
September 12, 2016 at 4:53 pm
I figured it out.
September 13, 2016 at 2:53 am
tlenzmeier (9/12/2016)
I figured it out.
Would you mind posting the solution so that people with a similar problem might benefit?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
September 13, 2016 at 6:58 am
My mistake was to grab Date.dim when I should have grabbed Due Date from the metadata tab. So the correct syntax is:
SELECT {Measures.[Reseller Sales Amount], Measures.[Reseller Sales Moving Average]} ON 0,
NONEMPTY([b][u]Due Date[/u][/b].[Calendar].[Month].Members) ON 1
FROM [Reseller Sales] -- reseller sales is the name of my cube
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply