adding columns

• astrid 69000

  Hall of Fame

  Points: 3208

  More actions

  March 28, 2013 at 7:12 am

  #149756

  hi,

  i have an mxd query that returns the data for yesterday (dynamic). what do i need to do to return the data from the day before yesterday in the second column and make a third column with an avg calculation of the first and second column.

  here is my code.

  with

  member [Measures].[Max] As max({[Hour Dim].[D Hour].[All].children},[Measures].[Purchase - converted to USD])

  member [Measures].[Min] As min({[Hour Dim].[D Hour].[All].children},[Measures].[Purchase - converted to USD])

  member [Measures].[Avg] As avg({[Hour Dim].[D Hour].[All].children},[Measures].[Purchase - converted to USD])

  member [Measures].[Void] As Aggregate(

  CROSSJOIN({strToMember("[CHB Date].[Year - Month - Period - Date].[Date].&[" + Format(dateadd("d",now(),-1), "yyyy-MM-ddT00:00:00") +"]") },

  { [Chargebacks].[CHB_Tree].[CHB Type].&[IRS VOID] }), [Measures].[Purchase - converted to USD])

  member [Measures].[CHB] As Aggregate(

  CROSSJOIN({strToMember("[CHB Date].[Year - Month - Period - Date].[Date].&[" + Format(dateadd("d",now(),-1), "yyyy-MM-ddT00:00:00") +"]") },

  {[Chargebacks].[CHB_Tree].[CHB Group].&[VOID],[Chargebacks].[CHB_Tree].[CHB Group].&[REFUND],[Chargebacks].[CHB_Tree].[CHB Group].&[ChargeBack]}), [Measures].[Purchase - converted to USD])

  select

  strToMember("[Time].[Year - Month - Period - Date].[Date].&[" + Format(dateadd("d",now(),-1), "yyyy-MM-ddT00:00:00") +"]") on columns,

  {[Measures].[Purchase - converted to USD],

  [Measures].[Purchases_Count],

  [Measures].[Site Pay User],

  [Measures].[Free_signups],

  [Measures].[free to site pay user],

  [Measures].[click to site pay user],

  [Measures].[Click Count],

  [Measures].[Guests Logins Count],

  [Measures].[Guests Unique Logged in],

  [Measures].[Guests Credit Spent],

  [Measures].[Max],

  [Measures].[Min],

  [Measures].[Avg],

  [Measures].[Void],

  [Measures].[CHB] } on rows