MDX - Monthly average open by date

Navigation
0

I need a lot of help.

I'm working with MDX for graphics generation 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 evolution, and I would like to add an average row in it. 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].[MEDIA] AS
AVG ([MES].[MES].CurrentMember, [Measures].[QTDE])
SELECT NON EMPTY {[Measures].[QTDE], [Measures].[MEDIA]} ON COLUMNS, 
       {[DATA].[DATA].Members} ON ROWS 
from [DW20_DIA]
where Crossjoin(Crossjoin(Crossjoin({[MES].[All MESs]}, {[CAUSE].[All CAUSEs]}), {[TIPO].[All TIPOs]}), {[MODEL].[All MODELs]})

With some filter: 

WITH 
MEMBER [Measures].[MEDIA] AS
AVG ([MES].[MES].Currentmember , [Measures].[QTDE])
SELECT NON EMPTY {[Measures].[QTDE], [Measures].[MEDIA]} ON COLUMNS, 
            NON EMPTY {[DATA].[DATA].Members} ON ROWS 
from [DW20_DIA]
where Crossjoin(Crossjoin(Crossjoin({[MES].[2016-11-01]}, {[STOP CAUSE].[All CAUSEs]}), {[TIPO].[All TIPOs]}), {[MODEL].[All MODELs]})

Sample data - In this case the average should be: 7,567,743 

  DATA        QNTD        MEDIA         MEDIA DEVE SER
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

----- Edition:

I gained access to objects and created a dimension of time. Year - Month - Day

I tried this query, but it did not work very well. 

WITH MEMBER [Measures].[MEDIA] AS 
Avg( Descendants([TEMPO].[MES].CURRENTMEMBER, [TEMPO].[DATA])
, [Measures].[QTDE]
)
SELECT {[Measures].[QTDE], [Measures].[MEDIA]} ON COLUMNS,
            NON EMPTY{[TEMPO].[DATA].MEMBERS} ON ROWS
FROM [DW20_DIA]

It repeats the same values for every day.

    
asked by anonymous 04.04.2017 / 20:49

1 answer

0

Use the Descendants

The Descendants function returns the members of a certain level from the specified member. So, you would have as a% of result% to apply to set dates that are daughters of the month of the current date. When you put in the line the days, for each line it will pick up the month of that day and search every day of that month and take the average with the values. 

WITH MEMBER [Measures].[MEDIA] AS 
   Avg( Descendants([TEMPO].CURRENTMEMBER.PARENT, [TEMPO].[DATA])
      , [Measures].[QTDE])
SELECT {[Measures].[QTDE], [Measures].[MEDIA]} ON COLUMNS,
       NON EMPTY{[TEMPO].[DATA].MEMBERS} ON ROWS
FROM [DW20_DIA]
    
05.04.2017 / 20:57
How to configure log4j in a java project that does not have the src folder? View with user-adjustable dynamic size