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

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

i'm trying aggregate (sum , average) on groups of quarters, i'm getting different results depending on method use. i'd understand why.

the next basic query illustrates info (pentaho steelwheels sample):

select {measures.sales, measures.quantity} on columns , order( time.quarters.members, time.currentmember.caption, basc ) on rows 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 row headers captions quarters , different occurrences of qtr1, qtr2, qtr3 , qtr4 each belong particular year (so 1st qtr1 [2003].[qtr1], 2nd [2004].[qtr1] , 3rd [2005].[qtr1], , on)

what i'd have result treats occurences of [qtr1] single group, , cell values aggregate sales quantity measure. let's want see @ to the lowest degree sum (total quantity quarters 1, 2, 3 , 4 regardless of years) , average (average quantity on quarters 1, 2, 3, , 4 regardless of years)

now, saw question:

how can merge 2 members 1 in query?

and while solution help requires enumerate quarters need grouping together. dynamically.

i came query:

with set orderedquarters order( time.quarters.members, time.currentmember.caption, basc ) set uniquequarters filter( orderedquarters, orderedquarters.item( orderedquarters.currentordinal-1 ).caption <> time.currentmember.caption ) fellow member measures.quantitysum sum( filter( orderedquarters, uniquequarters.item( uniquequarters.currentordinal ).caption = time.currentmember.caption ) , measures.quantity ) fellow member measures.counter count( filter( orderedquarters, uniquequarters.item( uniquequarters.currentordinal ).caption = time.currentmember.caption ) ) fellow member measures.[sum on count] measures.quantitysum / measures.counter fellow member measures.avgquantity avg( filter( orderedquarters, uniquequarters.item( uniquequarters.currentordinal ).caption = time.currentmember.caption ) , measures.quantity ) select {measures.quantitysum ,measures.counter ,measures.[sum on count] ,measures.avgquantity} on columns , uniquequarters on rows steelwheelssales

which gives result:

time | quantitysum | counter | sum on 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 [quantitysum] , [sum on count] measures give me result want, i'm not exclusively satisfied:

the [quantitysum] explicitly calculated sum. have implicitly calculated, since defined in cube sum aggregated measure i'm explictly calculating average in [sum on count] calculated measure. utilize avg() , tried measures.avgquantity, doesn't seem behave in way expect. looks caculates average q1, , repeats value other quarters well. why? sum , count seem work intended, why avg different?

this isn't reply per se, because haven't tried out , mdx knowledge old @ confident work. however, mentioned on twitter, wonder if next wouldn't work? specifically, using time.years.members list of members on average.

with set orderedquarters order( time.quarters.members, time.currentmember.caption, basc ) set uniquequarters filter( orderedquarters, orderedquarters.item( orderedquarters.currentordinal-1 ).caption <> time.currentmember.caption ) fellow member measures.quantitysum sum( filter( orderedquarters, uniquequarters.item( uniquequarters.currentordinal ).caption = time.currentmember.caption ) , measures.quantity ) fellow member measures.counter count( filter( orderedquarters, uniquequarters.item( uniquequarters.currentordinal ).caption = time.currentmember.caption ) ) fellow member measures.[sum on count] measures.quantitysum / measures.counter fellow member measures.avgquantity avg( time.years.members , measures.quantity ) select {measures.quantitysum ,measures.counter ,measures.[sum on count] ,measures.avgquantity} on columns , uniquequarters on rows steelwheelssales

mdx pentaho olap mondrian