OLAP subtotals including filtered members

DeepButi

New Member
Joined
Jul 14, 2015
Messages
22
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

Code:
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
Code:
(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
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

scottsen

Well-known Member
Joined
Mar 16, 2014
Messages
1,263
I don't think any MDX knowledgable people hang out here. It's too hard for mortals.

I'll try and ping one of the freaks who understand this stuff...
 

DeepButi

New Member
Joined
Jul 14, 2015
Messages
22
:) yes, MDX is really too complex. I have tried several places and found no help anywhere. Can you suggest any specific forum?

I will have to try to define a pivot table over the SAS cube ... didn't try this approach.

Thks for your answer
 

ChrisWebb

New Member
Joined
Sep 2, 2014
Messages
27

DeepButi

New Member
Joined
Jul 14, 2015
Messages
22
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.
 

DeepButi

New Member
Joined
Jul 14, 2015
Messages
22
I was trying to twist it a little bit more ... :) . Our time dimension has two hierarchies
Calendar: Year-month-day
Weeks : YearISO-Week-Weekday

so I coded it like this

Code:
SCOPE([Measures].[PreviousYearSales]);
    SCOPE([DimTime].[Calendar].[Day].MEMBERS);
            THIS =  ([Measures].[Sales], ParallelPeriod([DimTime].[Calendar].[Year]));
    END SCOPE;
    SCOPE([DimTime].[Weeks].[Weekday].MEMBERS);
            THIS = ([Measures].[Sales], ParallelPeriod([DimTime].[Weeks].[YearISO]));
    END SCOPE;
END SCOPE;
just to discover only the last scope, [Weeks] , is working. Switching the assignment and [Calendar] is now working but [Weeks] is not.

As a workaround I created a second named calculation, both non visible and a calculated member to select which one to show depending on context.

Is there any way to do it just with one named calculation as I tried?

Thks
 

DeepButi

New Member
Joined
Jul 14, 2015
Messages
22
That's exactly what I was trying ... I will have to read carefully all your blog posts :)

Thks again!
 

DeepButi

New Member
Joined
Jul 14, 2015
Messages
22
Aha! As per your comment dated Feb 5th 2014 on your own post, my (and your) approach only works for calculated members and I was trying to use it for the real mesure created from a named calculation.

So it seems I will have to deal with it as two separate hidden measures plus a calculated member. I can live with that :)

Anyway, thks a lot, all your posts are really great!
 

Watch MrExcel Video

Forum statistics

Threads
1,098,918
Messages
5,465,429
Members
406,427
Latest member
gboomer

This Week's Hot Topics

Top