MDX - Monthly Average Open by Date

Asked

Viewed 184 times

0

I need a lot of help.

I am working with MDX for graphics generation in Pentaho - CDE / Ctools. And I need to perform a series of filters that will be set by the user. The chart is a daily evolution, and I’d like to add a midline in it. But it’s not working out so well.

What am I doing wrong? I don’t know much about MDX. Should appear the values and repeat the average of the whole month, but 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]})

Data sample - 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

----- Editing:

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

I tried this query, but it didn’t 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.

pentaho mdx

  • Do you have a time dimension with hierarchy (year, month, day)? Or just separate dimensions of Day, Month and Year as it seems to be by the "Unique Names" that are on MDX? Normally you use a hierarchical date dimension by putting the day level on the line and use the "Descendants" function to average with members of the same dimension at the "day level"

  • @Pagotti, yes, they are two separate dimensions. They built the wrong objects. I just created a time dimension, I will try to assemble the query again. Thank you.

  • @Pagotti created a new dimension... but I haven’t yet...

  • [Tempo].CurrentMember instead of [Tempo].[Mes].CurrentMember helping?

  • @Pagotti If I put only Descendants([TEMPO].CURRENTMEMBER, [TEMPO].[MES]) and in select use NON EMPTY{[TEMPO].[DATA].MEMBERS} the mean column returns empty.

  • @Pagotti put on the board there, as it should appear... It should appear the same value for the whole month, and change when selected.

  • Maybe he’s not getting all the items. Try specifying to bring them all this way: Descendants([TEMPO].CURRENTMEMBER, [TEMPO].[DATA], SELF_BEFORE_AFTER) - I’m sorry the attempts is that I don’t have an OLAP environment to test.

  • Another way would be to seek the days by the parent member: Descendants([TEMPO].CURRENTMEMBER.PARENT, [TEMPO].[DATA]). I’ll put that option as an answer if it works.

  • Let’s go continue this discussion in chat.

  • @Pagotti... Perfect, Parent worked... Sorry for the delay, I had to leave for a meeting, I tested it here now and it worked perfectly...

Show 5 more comments

1 answer

0


Use the function Descendants

The function Descendants returns members of a certain level from the specified member. Thus, you would have as set result to apply in AVG the dates which are the daughters of the month of the current date. When putting the days on the line, for each line he will take the month of that day and search every day of that month and 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]

Browser other questions tagged pentaho mdx

You are not signed in. Login or sign up in order to post.