OLAP subtotals including filtered members

DeepButi

New Member
Joined
Jul 14, 2015
Messages
41
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
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...
 
Upvote 0
:) 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
 
Upvote 0
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
That's exactly what I was trying ... I will have to read carefully all your blog posts :)

Thks again!
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,212,947
Messages
6,110,837
Members
448,302
Latest member
sniffit1st

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top