Search code examples
mdxaveragepentahopentaho-cde

MDX - Daily chart with monthly average

I need a big help.

I am working with MDX to generate graphics in Pentaho - CDE / CTools. And I need to do a series of filters that will be set by the user. The chart is a daily progress, and I would like to add the average line, of the month. But it is not working very well.

What am I doing wrong? I do not have much knowledge with MDX. It should appear the values and repeat the average of the whole month, but it is repeating the same values.

WITH 
MEMBER [Measures].[AVG] AS
AVG ([MONTH].[MONTH].CurrentMember, [Measures].[QTD])
SELECT NON EMPTY {[Measures].[QTD], [Measures].[AVG]} ON COLUMNS, 
       {[DATE].[DATE].Members} ON ROWS 
FROM [DW20_DAY]
WHERE Crossjoin(Crossjoin(Crossjoin({[MONTH].[All MONTHs]}, {[CAUSE].[All CAUSEs]}), {[TYPE].[All TYPEs]}), {[MODEL].[All MODELs]})

With Filter:

WITH 
MEMBER [Measures].[AVG] AS
AVG ([MONTH].[MONTH].CurrentMember, [Measures].[QTD])
SELECT NON EMPTY {[Measures].[QTD], [Measures].[AVG]} ON COLUMNS, 
       {[DATE].[DATE].Members} ON ROWS 
FROM [DW20_DAY]
WHERE Crossjoin(Crossjoin(Crossjoin({[MONTH].[2017-03-01]}, {[CAUSE].[All CAUSEs]}), {[TYPE].[All TYPEs]}), {[MODEL].[All MODELs]})

Sample of data - In this case the average should be: 7.567.743

DATE        QTD         AVERAGE         It has to be
01/11/2016  7.731.442   7.731.442       7.567.743
02/11/2016  7.973.846   7.973.846       7.567.743
03/11/2016  7.430.333   7.430.333       7.567.743
04/11/2016  7.517.061   7.517.061       7.567.743
05/11/2016  6.738.677   6.738.677       7.567.743
06/11/2016  6.796.424   6.796.424       7.567.743
07/11/2016  7.631.584   7.631.584       7.567.743
08/11/2016  7.907.649   7.907.649       7.567.743
09/11/2016  8.995.933   8.995.933       7.567.743
10/11/2016  7.444.471   7.444.471       7.567.743
11/11/2016  8.039.431   8.039.431       7.567.743
12/11/2016  7.240.583   7.240.583       7.567.743
13/11/2016  6.779.103   6.779.103       7.567.743
14/11/2016  7.648.149   7.648.149       7.567.743
15/11/2016  7.641.452   7.641.452       7.567.743

---- EDIT:

I requested the creation of the time dimension. Now it's in the same hierarchy.

But I still can not solve it. In this case, how would it look?

WITH MEMBER [Measures].[AVG] AS 
Avg( Descendants([TIME].[MONTH].CURRENTMEMBER, [TIME].[DATE])
, [Measures].[QTDE]
)
SELECT {[Measures].[QTD], [Measures].[AVG]} ON COLUMNS,
       NON EMPTY{[TIME].[DATE].MEMBERS} ON ROWS
FROM [DW20_DAY]

Thanks.

Solution

  • The following code must work as well:

    Member [Measures].[AVG] as
AVG([DATE].[DATE].[DATE].Members, [Measures].[QTD])

    If you have a hierarchy Day -> Month and want to obtain AVG by parent, try:

    AVG([DATE].[DATE].CurrentMember.Siblings, [Measures].[QTD])