Grouping by Quarters with Mondrian / MDX: Behavior of Sum and Count vs Avg

Question

I'm trying to aggregate (Sum and Average) over groups of quarters, but I'm getting different results depending on which method I use. I'd like to understand why.

The following basic query illustrates my data (Pentaho SteelWheels sample):

SELECT  {Measures.Sales, Measures.Quantity} ON COLUMNS
,       ORDER(
          Time.Quarters.Members,
          Time.CurrentMember.Caption,
          BASC
        ) ON ROWS
FROM    SteelWheelsSales

The result:

Time |  Sales     |Quantity
-----+------------+---------
QTR1 |  445094.69 |  4561
QTR1 |  877418.97 |  8694
QTR1 | 1111260.1  | 10995
QTR2 |  564842.02 |  5695
QTR2 |  847713.97 |  8443
QTR2 |  869565.24 |  8480
QTR3 |  687268.87 |  6629
QTR3 | 1145308.08 | 11311
QTR4 | 1980178.42 | 19554
QTR4 | 2117298.82 | 20969

So the row headers are captions for the quarters and the different occurrences of QTR1, QTR2, QTR3 and QTR4 each belong to a particular year (So the 1st QTR1 is actually [2003].[QTR1], the 2nd is [2004].[QTR1] and the 3rd is [2005].[QTR1], and so on)

What I'd like to have is a result that treats all occurences of [QTR1] as a single group, and where the cell values aggregate the Sales Quantity measure. Let's say I want to see at least the sum (total quantity for all quarters 1, 2, 3 and 4 regardless of years) and the average (average quantity over all quarters 1, 2, 3, and 4 regardless of years)

Now, I saw this question:

How can I merge two members into one in a query?

And while that solution does help it requires that I enumerate the quarters that I need to group together. I would like to do that dynamically.

I came up with this query:

WITH
SET     OrderedQuarters
AS      Order(
          Time.Quarters.Members,
          Time.CurrentMember.Caption,
          BASC
        )
SET     UniqueQuarters
AS      Filter(
          OrderedQuarters,
          OrderedQuarters.Item(
            OrderedQuarters.CurrentOrdinal-1
          ).Caption <> Time.CurrentMember.Caption
        )
MEMBER  Measures.QuantitySum
AS      Sum(
          Filter(
            OrderedQuarters,
            UniqueQuarters.Item(
              UniqueQuarters.CurrentOrdinal
            ).Caption = Time.CurrentMember.Caption
          )
        , Measures.Quantity
        )
MEMBER  Measures.Counter
AS      Count(
          Filter(
            OrderedQuarters,
            UniqueQuarters.Item(
              UniqueQuarters.CurrentOrdinal
            ).Caption = Time.CurrentMember.Caption
          )
        )
MEMBER  Measures.[Sum over Count]
AS      Measures.QuantitySum / Measures.Counter
MEMBER  Measures.AvgQuantity
AS      Avg(
          Filter(
            OrderedQuarters,
            UniqueQuarters.Item(
              UniqueQuarters.CurrentOrdinal
            ).Caption = Time.CurrentMember.Caption
          )
        , Measures.Quantity
        )
SELECT {Measures.QuantitySum
       ,Measures.Counter
       ,Measures.[Sum over Count]
       ,Measures.AvgQuantity} ON COLUMNS
,       UniqueQuarters        ON ROWS
FROM    SteelWheelsSales

Which gives this result:

Time | QuantitySum | Counter |  Sum over Count  | AvgQuantity
-----+-------------+---------+------------------+----------------
QTR1 |       24250 |       3 |  8083 3333333333 | 8083.3333333333
QTR2 |       22618 |       3 |  7539.3333333333 | 8083.3333333333
QTR3 |       17940 |       2 |  8970            | 8083.3333333333
QTR4 |       40523 |       2 | 20261.5          | 8083.3333333333

Now, while the [QuantitySum] and [Sum over Count] measures give me the result I want, I'm not entirely satisfied:

The [QuantitySum] is explicitly calculated with Sum. I would like have it implicitly calculated, since it is already defined in the cube as a sum aggregated measure
I'm explictly calculating the average in the [Sum over Count] calculated measure. I would like to use Avg() and I tried with Measures.AvgQuantity, but that doesn't seem to behave in the way I expect. It looks like it caculates the average for Q1, and then it repeats that value for the other quarters as well. Why? Sum and Count seem to work exactly as intended, why is Avg so different?

Does the `Distinct` function work in Mondrian? https://msdn.microsoft.com/en-us/library/ms146033.aspx — whytheq, Apr 11 '15 at 14:49
If you add `Measures.Quantity` into the set ON ROWS what happens? If it is already defined with an aggregation type of Sum then does it not produce the same result as `[QuantitySum]` ? — whytheq, Apr 11 '15 at 15:00
In the definition of the measure `MEMBER Measures.AvgQuantity` what happens if you change `Measures.Quantity` to `Measures.QuantitySum`? — whytheq, Apr 11 '15 at 15:03
(hmmm... trying to transfer to an AdvWrks prototype but even your initial basic script acts a lot different in AdvWrks - straight away the result is aggregated by Q1,Q2,Q3,Q4) — whytheq, Apr 11 '15 at 15:44
@whytheq thanks for the replies. Mondrian has DISTINCT, but I don't see how it can help, since the apparently duplicate occurrences of QTR1, QTR2 etc aren't duplicates since each belongs to a different year. I cannot add Measures.Quantity to the ROWS axis since you cannot use the same hierarchy on more than one axis. If I use Measures.QuantitySum in the definition of Measures.AvgQuantity, I get the same result as Measures.QuantitySum - not the average. W/re to AdvWrks: that is really strange IMO. Is Quarter on a level below Year in that Cube? — Roland Bouman, Apr 11 '15 at 19:12
re. adding in `Measures.Quantity` - I meant ON COLUMNS not ROWS. re. AdvWrks they have two quarter hierarchies within the Date dimension. I will add a screen print so you can see their structure as it might give you some ideas — whytheq, Apr 11 '15 at 20:33

whytheq · Answer 1 · 2015-04-11T21:51:04.133

In terms of Microsofts AdvWrks cube the Date dimension is structured a little differently so that aggregates across Qrt1/Qtr2.. is easily achieved.

Here is the overall set of included hierarchies:

enter image description here

Here are the two hierarchies connected to quarters:

enter image description here

The level of the Date.Calendar user hierarchy includes the year in the member_caption:

enter image description here

Whereas the attribute hierarchy Date.Calendar Quarter of Year has captions like the ones in your scripts:

enter image description here

WITH 
  SET OrderedQuarters AS 
    Order
    (
      [Date].[Calendar Quarter of Year].[Calendar Quarter of Year].MEMBERS
     ,[Date].[Calendar Quarter of Year].CurrentMember.Member_Caption
     ,BASC
    ) 
  MEMBER Measures.AvgOrderCount AS 
    Avg
    (
      (EXISTING 
        [Date].[Calendar Year].[Calendar Year].MEMBERS)
     ,[Measures].[Order Count]
    ) 
SELECT 
  {
    [Measures].[Order Count]
   ,[Measures].[AvgOrderCount]
  } ON COLUMNS
 ,OrderedQuarters ON ROWS
FROM [Adventure Works];

It returns the following:

enter image description here

An alternative to the EXISTING function would be to cross join with the current member of the quarter hierarchy. This gives the same result:

WITH 
  SET OrderedQuarters AS 
    Order
    (
      [Date].[Calendar Quarter of Year].[Calendar Quarter of Year].MEMBERS
     ,[Date].[Calendar Quarter of Year].CurrentMember.Member_Caption
     ,BASC
    ) 
  MEMBER Measures.AvgOrderCount AS 
    Avg
    (
      ([Date].[Calendar Quarter of Year].CurrentMember*  //EXISTING 
        [Date].[Calendar Year].[Calendar Year].MEMBERS)
     ,[Measures].[Order Count]
    ) 
SELECT 
  {
    [Measures].[Order Count]
   ,[Measures].[AvgOrderCount]
  } ON COLUMNS
 ,OrderedQuarters ON ROWS
FROM [Adventure Works];

So referring to Ethen's answer if existing is not available in Mondrian then maybe this would force context to the calculation:

MEMBER  Measures.AvgQuantity
AS      Avg(
          (Time.Years.Members * Time.CurrentMember)
        , Measures.Quantity
        )

Building on the above I wonder if you cross join the filtered set with years:

MEMBER  Measures.AvgQuantity
AS      Avg(
          Filter(
            OrderedQuarters,
            UniqueQuarters.Item(
              UniqueQuarters.CurrentOrdinal
            ).Caption = Time.CurrentMember.Caption
          )
         * Time.Years.Members
        , Measures.Quantity
        )

so if I understand correctly, you're using a hierarchy that has unique quarters as the top level? In that case, achieving the correct aggregation is trivial. The point of my question was to aggregate on some datum (in my case, I used the Caption) that is tied to the members at a lower level and which is non-unique across the level. — Roland Bouman, Apr 11 '15 at 21:03
@RolandBouman my "answer" is really just a big comment - just thought you might be interested in the structure they have used. In the script I've added I've also used Year to average over, like Ethen. — whytheq, Apr 11 '15 at 21:07
thank you for your effort, I do appreciate you taking the time. I apologize for not making clear that I am mainly interested in the matter of why the MDX behaves like it does, and that I'm not directly interested in a solution of the business problem itself. So, I'd like a solution that doesn't rely on changing the structure of the hierarchy. — Roland Bouman, Apr 11 '15 at 21:17
@RolandBouman I just added another attempt where we cross join the filtered set of quarters with the years — whytheq, Apr 11 '15 at 21:52

score 1 · Answer 2 · answered Apr 11 '15 at 20:57

1

This isn't an answer per se, because I haven't tried it out and my MDX knowledge is too old to be at all confident that it will work. However, as mentioned on Twitter, I wonder if the following wouldn't work? Specifically, using Time.Years.Members as your list of members over which to average.

WITH
SET     OrderedQuarters
AS      Order(
          Time.Quarters.Members,
          Time.CurrentMember.Caption,
          BASC
        )
SET     UniqueQuarters
AS      Filter(
          OrderedQuarters,
          OrderedQuarters.Item(
            OrderedQuarters.CurrentOrdinal-1
          ).Caption <> Time.CurrentMember.Caption
        )
MEMBER  Measures.QuantitySum
AS      Sum(
          Filter(
            OrderedQuarters,
            UniqueQuarters.Item(
              UniqueQuarters.CurrentOrdinal
            ).Caption = Time.CurrentMember.Caption
          )
        , Measures.Quantity
        )
MEMBER  Measures.Counter
AS      Count(
          Filter(
            OrderedQuarters,
            UniqueQuarters.Item(
              UniqueQuarters.CurrentOrdinal
            ).Caption = Time.CurrentMember.Caption
          )
        )
MEMBER  Measures.[Sum over Count]
AS      Measures.QuantitySum / Measures.Counter
MEMBER  Measures.AvgQuantity
AS      Avg(
          Time.Years.Members
        , Measures.Quantity
        )
SELECT {Measures.QuantitySum
       ,Measures.Counter
       ,Measures.[Sum over Count]
       ,Measures.AvgQuantity} ON COLUMNS
,       UniqueQuarters        ON ROWS
FROM    SteelWheelsSales

answered Apr 11 '15 at 20:57

Ethan Jewett

6,002
16
25

Much appreciated :) Will try and work on this for a bit. I'll add a comment to let you know. At any rate, thanks, much obliged. – Roland Bouman Apr 11 '15 at 21:04
I agree with the use of years - the AdvWrks script I've posted also uses the year. Although I needed to add in EXISTING to get it to not return the same number for each avg – whytheq Apr 11 '15 at 21:04
Well, I tried and it turns out that returns exactly the same number as when I do WITH MEMBER Measures.AvgQuantity AS AVG(Time.Years.Members, Measures.Quantity) SELECT {Measures.Quantity, Measures.AvgQuantity} ON COLUMNS, Time.Years.Members ON ROWS FROM SteelWheelsSales It gives the average of quantity per year but it doesn't break the number down per quarter. I simply get a repeating value of 35110.333.. which is indeed the sum of all quantities divided by 3 (= the number of years) – Roland Bouman Apr 11 '15 at 21:15
@whytheq could you elaborate a little on how that Existing() is worked into the solution? I get a repeating number too. – Roland Bouman Apr 11 '15 at 21:21
EXISTING pulls the context of the ROWS/COLUMNS back up into the calculation. Is it available in Mondrian? If you add it into Ethan's answer does that help? – whytheq Apr 11 '15 at 21:22
@RolandBouman It will mean that only the year members that exist for each of the respective quarters on rows is used for the average calculation. – whytheq Apr 11 '15 at 21:27
@whytheq well, on the rows, I now have the quarters for the first year - that's how the UniqueQuarters set was constructed. So it would then take the average of only that year. I want it for all years, but only if the quarter caption matches. – Roland Bouman Apr 11 '15 at 21:33
1

@RolandBouman ok - yes indeed they are - my fault for not spotting that. So you are effectively trying to force aggregates next to (sort of) unrelated members on rows. – whytheq Apr 11 '15 at 21:44
1

@RolandBouman added one more attempt at the end of my answer. Of to bed now though - I've starred your question as this is a good one and I'm interested to hear the outcome. – whytheq Apr 11 '15 at 21:53

Grouping by Quarters with Mondrian / MDX: Behavior of Sum and Count vs Avg

2 Answers2