## MDX - calculating averages over time

### MDX - calculating averages over time

I'm trying to calculate the average value of a measure for a dimension
member over the number of days contained in the time range specified.

I have the following cube:
Volume Amount - Measure

CaptureDate - Dimension
(All) level
Year level
Month level
Day level

VolumeType - Dimension
(All) level
VolumeType level
Actual MDC  member
...

At any point in CaptureDate I need to divide the measure (Volume
Amount)
based on the number of days belonging to the chosen CaptureDate range.

For example,

SELECT {[Capture Date].[Year]..[October]} ON COLUMNS,
{[Volume Type].[Volume Type].[Actual ADC]} ON ROWS
FROM   [VolumesByDepartment]
WHERE  ([Measures].[Volume Amount])

returns a value of 6077.4  which is the sum of Volume Amount for
Actual ADC over the entire month.

I really want 196.04 which is 6077.4/the number of days in October.

If {[Capture Date].[Year].} ON COLUMNS I need
(sum of Volume Amount for 2003 for Acutal ADC)/number of days in 2003.

If {[Capture Date].[Year]..[Month].Children} ON COLUMNS
I want the value displayed in each month to be the sum for the month
divided by the number of days in the month.

Thanks,
Stewart

### MDX - calculating averages over time

Hi Stewart,

If you can be sure that the number of children at the Day level under the
current month is really the number you want to divide by (otherwise you
could create a member property at the month level giving the 'true' number
of days in the month), then you could do something like this:

WITH MEMBER MEASURES.[AVERAGE VOLUME AMOUNT] AS 'MEASURES.[VOLUME
AMOUNT]/COUNT(DESCENDANTS([CAPTURE DATE].CURRENTMEMBER, [CAPTURE
DATE].[DAY]))'
SELECT {[Capture Date].[Year]..[October]} ON COLUMNS,
{[Volume Type].[Volume Type].[Actual ADC]} ON ROWS
FROM   [VolumesByDepartment]
WHERE  ([Measures].[AVERAGE Volume Amount])

HTH,

Chris

--

Chris Webb
Consultant, Microsoft Services Switzerland
(to email: remove 'online.' from the address this was posted with)

Analysis Services project!

Disclaimer: This posting is provided 'AS IS' with no warranties, and confers
no rights.

Quote:> I'm trying to calculate the average value of a measure for a dimension
> member over the number of days contained in the time range specified.

> I have the following cube:
> Volume Amount - Measure

> CaptureDate - Dimension
>    (All) level
>     Year level
>     Month level
>     Day level

> VolumeType - Dimension
>    (All) level
>     VolumeType level
>        Actual MDC  member
>        ...

> At any point in CaptureDate I need to divide the measure (Volume
> Amount)
> based on the number of days belonging to the chosen CaptureDate range.

> For example,

> SELECT {[Capture Date].[Year]..[October]} ON COLUMNS,
>        {[Volume Type].[Volume Type].[Actual ADC]} ON ROWS
> FROM   [VolumesByDepartment]
> WHERE  ([Measures].[Volume Amount])

> returns a value of 6077.4  which is the sum of Volume Amount for
> Actual ADC over the entire month.

> I really want 196.04 which is 6077.4/the number of days in October.

> If {[Capture Date].[Year].} ON COLUMNS I need
> (sum of Volume Amount for 2003 for Acutal ADC)/number of days in 2003.

> If {[Capture Date].[Year]..[Month].Children} ON COLUMNS
> I want the value displayed in each month to be the sum for the month
> divided by the number of days in the month.

> Thanks,
> Stewart

### MDX - calculating averages over time

Thanks, Chris, but I just realized I left something out.
My numerator must account for days in the period where the ACTUAL
Volume Amount chosen does not have data.

My time dimension has days through the rest of the fiscal year because
I have BUDGETED volume types in the cube through year end.
I only have ACTUAL volumes through the previous day.

Because I have a [Volume Type].[Budgeted ADC Total] with a
[Measures].[Volume Amount] for every day in November I can't do this:

COUNT(filter(DESCENDANTS([CAPTURE DATE].CURRENTMEMBER, [CAPTURE
DATE].[DAY]), not isempty([measures].[volume amount])).

How do I filter the days in Capture Date's current member to get only
the days where [Measures].[Volume Amount] for [Volume Type].[Actual

Sorry I didn't include this in previous post.
Stewart

> Hi Stewart,

> If you can be sure that the number of children at the Day level under the
> current month is really the number you want to divide by (otherwise you
> could create a member property at the month level giving the 'true' number
> of days in the month), then you could do something like this:

> WITH MEMBER MEASURES.[AVERAGE VOLUME AMOUNT] AS 'MEASURES.[VOLUME
> AMOUNT]/COUNT(DESCENDANTS([CAPTURE DATE].CURRENTMEMBER, [CAPTURE
> DATE].[DAY]))'
> SELECT {[Capture Date].[Year]..[October]} ON COLUMNS,
>        {[Volume Type].[Volume Type].[Actual ADC]} ON ROWS
> FROM   [VolumesByDepartment]
> WHERE  ([Measures].[AVERAGE Volume Amount])

> HTH,

> Chris

> --

> Chris Webb
> Consultant, Microsoft Services Switzerland
> (to email: remove 'online.' from the address this was posted with)

> Microsoft Services Switzerland and our partners can help you with your
> Analysis Services project!

> Disclaimer: This posting is provided 'AS IS' with no warranties, and confers
> no rights.

> > I'm trying to calculate the average value of a measure for a dimension
> > member over the number of days contained in the time range specified.

> > I have the following cube:
> > Volume Amount - Measure

> > CaptureDate - Dimension
> >    (All) level
> >     Year level
> >     Month level
> >     Day level

> > VolumeType - Dimension
> >    (All) level
> >     VolumeType level
> >        Actual MDC  member
> >        ...

> > At any point in CaptureDate I need to divide the measure (Volume
> > Amount)
> > based on the number of days belonging to the chosen CaptureDate range.

> > For example,

> > SELECT {[Capture Date].[Year]..[October]} ON COLUMNS,
> >        {[Volume Type].[Volume Type].[Actual ADC]} ON ROWS
> > FROM   [VolumesByDepartment]
> > WHERE  ([Measures].[Volume Amount])

> > returns a value of 6077.4  which is the sum of Volume Amount for
> > Actual ADC over the entire month.

> > I really want 196.04 which is 6077.4/the number of days in October.

> > If {[Capture Date].[Year].} ON COLUMNS I need
> > (sum of Volume Amount for 2003 for Acutal ADC)/number of days in 2003.

> > If {[Capture Date].[Year]..[Month].Children} ON COLUMNS
> > I want the value displayed in each month to be the sum for the month
> > divided by the number of days in the month.

> > Thanks,
> > Stewart

### MDX - calculating averages over time

No problem Stewart, it should be as simple as modifying your statement to:

COUNT(filter(DESCENDANTS([CAPTURE DATE].CURRENTMEMBER, [CAPTURE
DATE].[DAY]), not isempty(([measures].[volume amount],[Volume Type].[Actual

HTH,

Chris

--

Chris Webb
Consultant, Microsoft Services Switzerland
(to email: remove 'online.' from the address this was posted with)

Analysis Services project!

Disclaimer: This posting is provided 'AS IS' with no warranties, and confers
no rights.

> Thanks, Chris, but I just realized I left something out.
> My numerator must account for days in the period where the ACTUAL
> Volume Amount chosen does not have data.

> My time dimension has days through the rest of the fiscal year because
> I have BUDGETED volume types in the cube through year end.
> I only have ACTUAL volumes through the previous day.

> Because I have a [Volume Type].[Budgeted ADC Total] with a
> [Measures].[Volume Amount] for every day in November I can't do this:

> COUNT(filter(DESCENDANTS([CAPTURE DATE].CURRENTMEMBER, [CAPTURE
> DATE].[DAY]), not isempty([measures].[volume amount])).

> How do I filter the days in Capture Date's current member to get only
> the days where [Measures].[Volume Amount] for [Volume Type].[Actual
> ADC Total] is not empty)?

> Sorry I didn't include this in previous post.
> Stewart

- Show quoted text -

> > Hi Stewart,

> > If you can be sure that the number of children at the Day level under
the
> > current month is really the number you want to divide by (otherwise you
> > could create a member property at the month level giving the 'true'
number
> > of days in the month), then you could do something like this:

> > WITH MEMBER MEASURES.[AVERAGE VOLUME AMOUNT] AS 'MEASURES.[VOLUME
> > AMOUNT]/COUNT(DESCENDANTS([CAPTURE DATE].CURRENTMEMBER, [CAPTURE
> > DATE].[DAY]))'
> > SELECT {[Capture Date].[Year]..[October]} ON COLUMNS,
> >        {[Volume Type].[Volume Type].[Actual ADC]} ON ROWS
> > FROM   [VolumesByDepartment]
> > WHERE  ([Measures].[AVERAGE Volume Amount])

> > HTH,

> > Chris

> > --

> > Chris Webb
> > Consultant, Microsoft Services Switzerland
> > (to email: remove 'online.' from the address this was posted with)

> > Microsoft Services Switzerland and our partners can help you with your
> > Analysis Services project!

> > Disclaimer: This posting is provided 'AS IS' with no warranties, and
confers
> > no rights.

> > > I'm trying to calculate the average value of a measure for a dimension
> > > member over the number of days contained in the time range specified.

> > > I have the following cube:
> > > Volume Amount - Measure

> > > CaptureDate - Dimension
> > >    (All) level
> > >     Year level
> > >     Month level
> > >     Day level

> > > VolumeType - Dimension
> > >    (All) level
> > >     VolumeType level
> > >        Actual ADC  member
> > >        Actual MDC  member
> > >        ...

> > > At any point in CaptureDate I need to divide the measure (Volume
> > > Amount)
> > > based on the number of days belonging to the chosen CaptureDate range.

> > > For example,

> > > SELECT {[Capture Date].[Year]..[October]} ON COLUMNS,
> > >        {[Volume Type].[Volume Type].[Actual ADC]} ON ROWS
> > > FROM   [VolumesByDepartment]
> > > WHERE  ([Measures].[Volume Amount])

> > > returns a value of 6077.4  which is the sum of Volume Amount for
> > > Actual ADC over the entire month.

> > > I really want 196.04 which is 6077.4/the number of days in October.

> > > If {[Capture Date].[Year].} ON COLUMNS I need
> > > (sum of Volume Amount for 2003 for Acutal ADC)/number of days in 2003.

> > > If {[Capture Date].[Year]..[Month].Children} ON COLUMNS
> > > I want the value displayed in each month to be the sum for the month
> > > divided by the number of days in the month.

> > > Thanks,
> > > Stewart

Hello:

I'm currently working on a retail cube solution, and I'm stuck with the
Average Stock indicator.

The indicator works thus:

a) The following applies for the Store N1, Product N 1,
for the first three days of a given week:

On Monday, Stock = 4
On Tuesday, Stock = 6
On Wednesday, Stock = 8

For the three days, the Average Stock for Store N1, Product N 1,
= 6 = (4+6+8) / Distinct Number of Days = 18 / 3

b) Now, the following applies for the Store N2, Product N 1,
for the first three days of a given week:

On Monday, Stock = 6
On Tuesday, Stock = 8
On Wednesday, Stock = 13

For the three days, the Average Stock for Store N2, Product N 1,
= 9 = (6+8+13) / Distinct Number of Days = 27 / 3

c) Finallly, considering both Store N1 and Store N2, for Product N 1,

On Monday, Stock = 10
On Tuesday, Stock = 14
On Wednesday, Stock = 21

So, for the three days, the Average for Store N1 and Store N2, Product
N 1,
= 15 = (10+14+21) / Distinct Number of Days = 45 / 3

As you can see, this is a rather complex indicator, that for any given
period (a specific day, week, month, etc) is the SUM() of its individual
members values, but across a number of days, you have to divide by the
disticnt
number of days.

In fact, the formula

[Average Stock] = Iif([Time].CurrentMember.Children.Count = 0 ,
[Measures].[Stock],
[Measures].[Stock]/[Measures].[Distinct Days])

does gives the required results, having the primitive Measures defined as:

[Stock]= SUM("dbo"."FT"."Stock")
[Distinct Days] = Distinct(Count("dbo"."FT"."Date"))

The problem with this approach is that if you try to display this data
on EXCEL (with Pivot Table Service) and you select a subset of values of
the
[Time] dimension (like Monday and Wednesday, excluding Tuesday), the
[Distinct Days] indicator gets undefined and so does the [Average Stock]
calulated Member.

On the other hand, you can try the following formula:

[Average Stock] = AVG(
{
Descendants([Time].CurrentMember,
[Time].Levels.Count-[Time].CurrentMember.Level.Ordinal,
LEAVES)
[Measures].[Stock]
)

It works exactly as the "Distinct Days" approach, but if you select a
subset of values
of the [Time] dimension (like Monday and Wednesday, excluding Tuesday), the
[Average Stock] member isn't undefined, but it doesn't reflect de selected
days data;
it reflects the data of the three days, ignoring the days excluded.

Both approaches behave exactly the same, wether you select a subset of
days, weeks,
months, years or whatever hierarchy levels you define for the [Time]
dimension.
One goes undefined, the other ignores your selection.

If anyone has any clue whatsoever at some way to solve this problem,