1

We are implementing ActivePivot at a logistics service provider and we would like to build a view to help us find out the shipments that are not moving fast enough, and help understand how to improve this KPI. We have a simple MDX view that shows aged inventory in days as well as the number of shipments by customers.

Is there a simple way to compute the aged inventory weighted by the volume of shipments of each customer ? Is there a way to compute this weighted average regardless of the dimension used to analyze this measure ?

Here is the MDX query that we use:

WITH
MEMBER [Measures].[Mean Storage Time] AS
    ([Measures].[Nb.DayPrestation.Started-Finished].Value / [Measures].[Nb.Prestation.Started-Finished].Value)
SELECT
NON EMPTY Order(Hierarchize({DrilldownLevel({[V_MODEL].[ALL].[AllMember]})}), ([Measures].[Mean Storage Time]), DESC) ON ROWS,
NON EMPTY {[Measures].[Mean Storage Time], [Measures].[NbP.PBK]} ON COLUMNS
FROM [OperationPrestationCube]
WHERE ([V_DESTINATION].[ALL].[AllMember], [P_STOCKAGE].[ALL].[AllMember].[true])
user1731897
  • 11
  • 3

2 Answers2

1

The following MDX adds a "Weighted Mean Storage" which is equals to "Mean Storage Time" weighted by "NbP.PBK"

WITH
MEMBER [Measures].[Weighted Mean Storage] AS
    Iif(([V_MODEL].CurrentMember.Level.Ordinal = 0), [Measures].[Mean Storage Time], Iif(IsEmpty([Measures].[Mean Storage Time]), NULL,(([Measures].[Mean Storage Time] * [Measures].[NbP.PBK]) / ([V_MODEL].DefaultMember, [Measures].[NbP.PBK]))))
MEMBER [Measures].[Mean Storage Time] AS
    ([Measures].[Nb.DayPrestation.Started-Finished].Value / [Measures].[Nb.Prestation.Started-Finished].Value)
SELECT
NON EMPTY Order(Hierarchize({DrilldownLevel({[V_MODEL].[ALL].[AllMember]})}), ([Measures].[Mean Storage Time]), DESC) ON ROWS,
NON EMPTY {[Measures].[Mean Storage Time], [Measures].[NbP.PBK], [Measures].[Weighted Mean Storage]} ON COLUMNS
FROM [OperationPrestationCube]
WHERE ([V_DESTINATION].[ALL].[AllMember], [P_STOCKAGE].[ALL].[AllMember].[true])
blacelle
  • 2,199
  • 1
  • 19
  • 28
0

You can compute the weighted average with an expression like this one:

Sum('set on which you want to do weighted avg', 'your measure' * 'the weight') / Sum('set on which you want to do weighted avg', 'the weight')
Benoit
  • 1,995
  • 1
  • 13
  • 18