OLAP subtotals including filtered members

[DeepButi]

I'm stuck with subtotals and a calculated member for previous period (year). No matter what I do, when filtering by date the calculated "previous year" value includes the filtered members, making unusable the whole table.

I'm using MS SQL SAS 11.0.5058.0 ans MS Excel 2013

For a time dimension Year-Month-Day this is an example of what I get selecting one day

Year    Month   Day   Previous Sales          Sales
2015    04       03      74,154.56 €     135,156.41 €
    Total 04           2,617,045.75 €     135,156.41 €
Total 2015          37,696,665.69 €     135,156.41 €

Where Previous sales has been defined as

(ParallelPeriod([Dim Time].[Calendar].[Year],1,[Dim Time].[Calendar].CurrentMember),[Measures].[Sales])

Sales (a base measure) or even a calculated member with no time involved (for example [Measures].[Sales]-[Measures].[Cost]) shows a correct subtotal provided Excel option "Include filtered elements in totals" is off.

Tried suggestions here and here without success.

the problem arises not only with subtotals, but any other dimension added is also affected. Time+product category for example on rows ... as time filter is not honoured, all Product category values include all days/months of the year, not the selected ones.

So, in fact we cannot use Excel!! This must be a common problem as I can hardly imagine any single serious business scenario without previous periods analysys, but despite hours(days!) of searching I didn't find any solution.

Any help will be appreciated, this is making me nuts!

Thks

 

[Josez]

Chris,
you are my hero! I have been trying to find an answer for weeks and yours is the first real solution that really works.

Not even needed to define the "product" scope, just with the time dimension scope all problems (filtering, adding other dimensions to the rows, etc) were solved.

Plain and easy!

Thanks again.

Hi all,
I'm in the same point as you, but I tried some combinations of use of the SCOPE command with no succeed! (I'm relative new in MDX and I read Chris Webb blog, but MDX is hard...)
I have 2 time dimensions but calculated measures are only in one of them. Maybe the time dimension structure can affect? Any idea please!? I'm stuck...

Thank you very much.

 

[ChrisWebb]

Can you post the MDX code you're using and a few more details about the structure of your cube and dimensions?

Chris

 

[Josez]

Can you post the MDX code you're using and a few more details about the structure of your cube and dimensions?

Chris

Hi Chris, thanks for your interest.
We have a cube with 2 identical time dimensions. And calculated measures (PY, and YTD) based in one of them (we only want to treat one temporal dimension).

This is the definition of Time dimension we want to use "D Fecha Insercion" and the used user hierarchy for it "Jerarquia FechaInsercion Intervalos" (sorry, it's in Spanish...) :

The Attribute Relationship is:
Id Fecha --> Dia De La Semana --> Semana Del Mes --> Mes --> DescripcionCortaAnyoMes --> DescripcionCortaMes --> Trimestre --> Semestre --> Anyo

Where:
- Id Fecha: Date. It has a integer YYYYMMDD as KeyColumn and the same as varchar as NameColumn
- Dia De La Semana: Day of week (1..7)
- Semana Del Mes: Week of the month (1..5)
- Mes: Month. It has a integer YYYYMM as KeyColumn and (January..December) as NameColumn to show to the user.
- DescripcionCortaAnyoMes: Month too... but it has a Namecolumn whith varchar YYYYDD
- DescripcionCortaMes: Another Month... but it has a JAN..DEC as NameColumn
- Trimestre: Quarter
- Semestre: Half Year
- Anyo: Year

The code (whitout scope) whe use for PY and YTD, based in a amount measure "[M Importe Insercion Neto]":

CREATE MEMBER CURRENTCUBE.[Measures].PY_Importe_Neto
AS ([Measures].[M Importe Insercion Neto],ParallelPeriod ([D Fecha Insercion].[Jerarquía FechaInsercion Intervalos].[Anyo]
, 1
, [D Fecha Insercion].[Jerarquía FechaInsercion Intervalos].CurrentMember)
),
VISIBLE = 1 , ASSOCIATED_MEASURE_GROUP = 'TH PUBLICIDAD' ;

CREATE MEMBER CURRENTCUBE.[Measures].YTD_Importe_Neto
AS SUM(
YTD([D Fecha Insercion].[Jerarquía FechaInsercion Intervalos].CurrentMember),
[Measures].[M Importe Insercion Neto]
),
VISIBLE = 1 , ASSOCIATED_MEASURE_GROUP = 'TH PUBLICIDAD';


We tested several uses of Scope (some with no sense... only for desperation) I show only some examples for PY:

1)
CREATE MEMBER CURRENTCUBE.MEASURES.[PY_Importe_Neto] AS NULL,
VISIBLE = 1 , ASSOCIATED_MEASURE_GROUP = 'TH PUBLICIDAD';

SCOPE(MEASURES.[PY_Importe_Neto]);
Scope([D Fecha Insercion].[id Fecha].members);
THIS =(ParallelPeriod([D Fecha Insercion].[Jerarquía FechaInsercion Intervalos].[Anyo],
1,
[D Fecha Insercion].[Jerarquía FechaInsercion Intervalos].CurrentMember),[Measures].[M Importe Insercion Neto]
);
END SCOPE;
END SCOPE;

2)
Scope([D Fecha Insercion].[Anyo].[Anyo].members,[D Fecha Insercion].[Id Fecha].members);
[Measures].[PY_Importe_Neto] = (ParallelPeriod([D Fecha Insercion].[Jerarquía FechaInsercion Intervalos].[Anyo],
1,
[D Fecha Insercion].[Jerarquía FechaInsercion Intervalos].CurrentMember),[Measures].[M Importe Insercion Neto]
);
END SCOPE;

And so on... with no succeed.
Maybe we are not understanding well the use of Scope or there are something in our Time dimension... don't know.

Thank you very much in advance Chris.

 

[Josez]

Returning to the problem, clarifying the question ...
When we filter the time dimension (i.e. a Quarter in this case) the calculated measures are not filtering and show the total for the year, not only the period whe are filtering, i.e.:

The Total of the column is also missed.

 

[ChrisWebb]

I see what the problem is, I think - you are using calculated measures. In order for this to work you cannot use calculated measures, you have to create regular measures and then overwrite their value using SCOPE statements in the way described in this blog post: Aggregating the Result of an MDX Calculation Using Scoped Assignments – Chris Webb's BI Blog

Chris

 

[Josez]

OK Chris, thank you for your quick answer.
I'll read your post and we'll try with this scenario. I suppose that we'll get some doubt... if that, I'll back to you.

Thank you very much again for your time!
Bye.

 

[Josez]

Hello again,
we tried that you said Chris (with a big help from a forum user...) and it's working. Thank you very very much!
I created regular measures directly in the View, and later using the Scope with the fórmula. The only problem for Previous Year is the use of ParallelPeriod... It isn't calendar aware and the result is incorrect when the previous year is a leap year.
I'm thinking about... to get a solution.

Thank you very much again.
Bye.

 

[COPIN]

Hello,
Can you post the last MDX code you're using Josez please ?

Thank you very much
bye

 

[DeepButi]

Hi COPIN,
two steps:

1. In your view create a named calculation, i.e. PreviousSales under the SalesTable table, with expression null. Be carefull, a named calculation in the table on your view.
2. In your cube, on the Calculations tab, open it in script mode and add

SCOPE([Measures].[PreviousSales]);
SCOPE([Dim Time].[Month].[Day].MEMBERS);
THIS = ([Measures].[Sales], ParallelPeriod([Dim Time].[Month].[Year]));
BACK_COLOR(THIS) = RGB(64,128,255);
END SCOPE;
END SCOPE;

Read carefully the link provided by Chris, it's all there!

 

[COPIN]

Hi DeepButi,

Thank you for our quick answer
I read the link provided by Chris. It's very clear.
I was not sure with the parallelperiod.
I managed to create my measure.
Thank you very very much !!!!!
bye