Calculating Maximum Amount from a Running Total per Account

• Tris DL

  Valued Member

  Points: 72

  More actions

  November 21, 2012 at 10:10 am

  #151121

  Hi,

  I hope somebody can help. I need to calculate a maximum amount based upon a daily running total (per account) within MDX/SSAS and have this displayed in the cube.

  I have created an example running total for daily sales amounts.

  WITH MEMBER [Measures].[RunningTotal] AS

  aggregate(

  PeriodsToDate([Calendar].[Date Hierarchy].[All].level,[Calendar].[Date Hierarchy].currentmember

  ),[Measures].[Amt])

  SELECT

  {[Measures].[Amt], [Measures].[RunningTotal]} ON 0,

  [Account].[AccountId].members * [Calendar].[Date Hierarchy].[Date].members on 1

  FROM

  [MyCube];

  EG data output:

  AccID Date Amt RunningTotal

  Acc12012-01-0100

  Acc12012-01-02 22

  Acc12012-01-03 35

  Acc12012-01-04 -14

  Acc12012-01-05 48 --This being the MAX running total value.

  Acc12012-01-06 -53

  Acc12012-01-07 03

  Acc12012-01-08 03

  Acc12012-01-09 14

  In essence, the Amt equates to a transaction amount. The generated running total is a balance (e.g. Standalone versus cumulative values).

  ...Aim is to provide a CalcMeasure which will have Acc1 = 8.

  I've unsuccessfully tried to capture the MAX SalesAmt by Date using RANK() and HEAD() functions. My attempts appear to change the nature of the running total.

  Also not sure how best to approach only applying the running total to [Account].[AccountId]. Would this be best acheived via SCOPE statement?

• Tris DL

  Valued Member

  Points: 72

  More actions

  November 23, 2012 at 10:16 am

  #1561897

  Have calculated the MAX over the running total:

  WITH MEMBER [Measures].[RunningTotal] AS

  aggregate(

  PeriodsToDate([Calendar].[Date Hierarchy].[All].level,[Calendar].[Date Hierarchy].currentmember

  ),[Measures].[Amt])

  MEMBER [Measures].[MaxAmt] AS

  MAX(EXISTING [Calendar].[Date Hierarchy].[Date].members, [Measures].[RunningTotal])

  SELECT

  {[Measures].[Amt], [Measures].[MaxAmt]} ON 0,

  [Account].[AccountId].&[Acc1] on 1

  FROM

  [MyCube];