SSAS - Moving Annual Total

views:

answers:

SSAS - Moving Annual Total

Environment: SQL-Server-2005, SSAS 2005

I am using the following formula to create a Moving Annual Total

SUM ({[Time].CurrentMember.Lag(3):[Time].CurrentMember}, [Measures].[TRx Quantity])

The same column [Measures].[Trx Quantity] is used in other calculations and it works fine. This one, however, comes up with #Value! to indicate some kind of an error. Can anyone point me in the proper direction here?

+2 A:

Is this SSAS 2005/2008?

If it is either of these and you are running this in Management Studio you can hover over the #Value and get the details of the error.

But at a guess it is most likely because .CurrentMember needs to be called on a hierarchy and [Time] is a dimension reference. It should refer to one of the hierarchies like [Time].[Calendar].CurrentMember or maybe [Time].[Quarter].CurrentMember

Darren Gosbell 2009-10-13 05:23:45

I am on SQL Server 2005

Raj More 2009-10-13 11:57:14

It turned out to be that I was missing the MONTH

SUM ({[Time].[Month].CurrentMember.Lag(3):[Time].[Month].CurrentMember}, [Measures].[TRx Quantity])

Raj More 2009-10-13 22:48:33

Yes, that's what I said in my answer. In SSAS 2005 (and above) your uniquename references should be in the form of [Dimension].[Hierarchy].CurrentMember and [Month] is a hierarchy of the [Time] dimension.

Darren Gosbell 2009-10-13 23:47:54

@Darren: In my time dimension, [Month] is not a hierarchy - it is just a regular column. It said something to the effect that it needs a hierarchy. I tried creating a hierarchy and putting the name in, but then I used simple logic and figured out that it was asking me for a reference to the [Month]

Raj More 2009-10-14 13:22:41

ansaurus

tags:

views:

answers:

SSAS - Moving Annual Total

related questions