finding out a prev member

• Anamika

  SSCertifiable

  Points: 6202

  More actions

  January 5, 2009 at 3:39 am

  #203613

  hi

  i'm using SSAS 2008.

  i want to include prevmember column on the following query.

  so that i'll get [quantity sold] for [Day Week Id].&[0728] also.

  (i.e.Prev year same period)

  SELECT

  NON EMPTY { [Measures].[Quantity Sold] } ON COLUMNS,

  NON EMPTY

  { ([Client Tbl Dim Calendar].[Day Week Id].[Day Week Id].ALLMEMBERS *

  [Client Tbl Dim Product].[Category].[Category].ALLMEMBERS *

  [Client Tbl Dim Stores].[Store Name].[Store].ALLMEMBERS ) }

  DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS

  FROM ( SELECT ( { [Client Tbl Dim Stores].[Store].&[BigW - Altona Meadows] } ) ON COLUMNS

  FROM ( SELECT ( { [Client Tbl Dim Product].[Category].&[Mens Belts] } ) ON COLUMNS

  FROM ( SELECT ( { [Client Tbl Dim Calendar].[Day Week Id].&[0828] } ) ON COLUMNS

  FROM [DSVClientAdmin])))

  Thanks,

  Regards

  viji

• Dave Balsillie

  SSCertifiable

  Points: 5589

  More actions

  January 6, 2009 at 12:54 pm

  #922080

  Create a calculation as in:

  [Code]

  With

  Member [Prev Quantity Sold] as ( [Quantity Sold], ([Client Tbl Dim Calendar].[Day Week Id].CurrentMember.PrevMember ), Format_String= "#,#"

  SELECT

  { [Quantity Sold], [Prev Quantity Sold] } ON COLUMNS,

  etc.

  [/Code]

  Reminder: The first member doesn't have a previous member, so you'll have to either eliminate it from your set, or beef up your calculation syntax.

• Anamika

  SSCertifiable

  Points: 6202

  More actions

  January 6, 2009 at 8:21 pm

  #922299

  Thanks, the same kind a query got worked.

  Regards

  Viji