Calculating the average over time

Calculating the average over time

• Mach1

  Yes that is what I did:

  Create member currentcube.[Measures].[Average subscriptions SoP] as
  avg ( [Date Time].[Day].CurrentMember, [Measures].[Active subscriptions SoP] )
  , visible=true;

  
  

• leclerc9

  (.Isn't it a better idea to put that avg in a calculated member (kinda like a measure))
  
  Well, I am also a bit of a noob, so I can't help you much with it :(
  
  -edit-
  Of course, you get the same, if the granularity is the same
  
  

• Adam Miles

  Though it has no direct link with this subject, the following link might have a clue:
  http://msdn2.microsoft.com/en-us/library/aa902637(SQL.80).aspx
  
  quote from the link:
  

• JasonG271009

  Ah Yes, well, isn't it maybe then again a better idea to try it out in a normal mdx command, with a measure created with the "AS" command
  Just to try
  
  

• ProjectDev

  Hi all,

  After a few hours playing with the different functions, measures and scopes I found a solution that I wanted to share with you guys. First of all I created a method for counting the number of days for the selected time-member. I use a recursive measure to accomplish this:

  create member currentcube.[measures].[DayCount] as

  sum

  (

  [Date Time].[Week hierarchy].CurrentMember.Children,

  [Measures].[DayCount]

  )

  ,visible=false;

  SCOPE ([Measures].[DayCount]);

  [Date Time].[Week hierarchy].[Day] = 1;

  [Date Time].[Month hierarchy].[Month code] = sum([Date Time].[Month hierarchy].CurrentMember.Children, [Measures].[DayCount]);

  [Date Time].[Month hierarchy].[Quarter] = sum([Date Time].[Month hierarchy].CurrentMember.Children, [Measures].[DayCount]);

  END SCOPE;

  I use two different hierarchies in my Date/Time dimension, that’s why the two last lines in the scope part are added.

  Next I have to sum up all the active subscriptions at the start of the period (Active Subscriptions SoP), so I’ve created a measure that works almost the same way as the DayCount:

  create member CurrentCube.[Measures].[Sum Subscriptions SoP] as

  sum

  (

  [Date Time].[Week hierarchy].CurrentMember.Children,

  [Measures].[Sum subscriptions SoP]

  )

  ,visible=false;

  SCOPE ([Measures].[Sum Subscriptions SoP]);

  [Date Time].[Week hierarchy].[Day] = [Measures].[active subscriptions SoP];

  [Date Time].[Month hierarchy].[Month code] = sum([Date Time].[Month hierarchy].CurrentMember.Children, [Measures].[Sum subscriptions SoP]);

  [Date Time].[Month hierarchy].[Quarter]= sum([Date Time].[Month hierarchy].CurrentMember.Children, [Measures].[Sum subscriptions SoP]);

  END SCOPE;

  After this I only have to create a measure that takes the [Sum Subscriptions SoP] and divides it by [DayCount] :

  create member currentcube.[Measures].[Average subscriptions SoP] as

  [Measures].[Sum subscriptions SoP] / [Measures].[DayCount]

  ,visible=false;

  I’ve created the same set of measures for the End of Period measures (Active Subscriptions EoP). To finish my [Average Subscriptions] measure I simply add both measures and divide them by 2:

  create member currentcube.[Measures].[Average subscriptions] as

  ([Measures].[Average subscriptions SoP]+[Measures].[Average subscriptions EoP])/2
  ,visible=true;

  
  

• vsnetdeveloper

  Isn't there an AVG command in MDX
  Anyhow, here's maybe a usefull link:
  
  http://www.databasejournal.com/article.php/1459531/
  (scroll down until:  MDX Essentials Series )
  
  from:
  
  http://www.mosha.com/msolap/mdx.htm
  
  

• Bart Vercauteren

  Yes there is an AVG(<<Set>>[, <<Numeric Expression>>]) but this gives some strange and unwanted results, maybe I'm missing something. When I create the member like this:

  avg ( [Date Time].[Day].CurrentMember, [Measures].[Active subscriptions SoP] )

  It will allways return the same value als [Measures].[Active subscriptions SoP], so I'm clearly missing the point here :)

  I've read the articles you refer to, but I could not find the answer in there... That is, I'm probably to 'newby' to find it.

  
  

• pangitko79

  Eyso Zanstra wrote:

  Of course, you get the same, if the granularity is the same

  Yes that is of course true, but the week average is the same as the last day average and that is what is strange about it...

  
  

• Pipz

  Nono, I meant, when accessing data, not when creating a member.
  Like with the following example:
  

  WITH
   MEMBER Measures.Profit AS 'Measures.Sales – Measures.Cost'
   MEMBER Measures.ProfitPercent AS 'Measures.Profit / Measures.Cost', FORMAT_STRING = '#.#%'
  SELECT
   { Measures.Profit, Measures.ProfitPercent } ON COLUMNS
  FROM Sales

  
  from: http://www.mosha.com/msolap/articles/MDXForEveryone.htm
  
  
• Calculating the average over time