Page 1 of 3 123 LastLast
Results 1 to 10 of 21

Thread: OLAP subtotals including filtered members
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jul 2015
    Posts
    19
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default OLAP subtotals including filtered members

    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

  2. #2
    Board Regular scottsen's Avatar
    Join Date
    Mar 2014
    Location
    Seattle, WA
    Posts
    1,263
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: OLAP subtotals including filtered members

    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...
    scott at tinylizard.com : Power Pivot Consultant, Trainer and Mercenary
    See my Power Pivot blog at http://tinylizard.com/blog

  3. #3
    New Member
    Join Date
    Jul 2015
    Posts
    19
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: OLAP subtotals including filtered members

    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

  4. #4
    New Member
    Join Date
    Sep 2014
    Location
    Amersham, UK
    Posts
    27
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: OLAP subtotals including filtered members

    This is expected behaviour for calculated measures; I can see why this is causing a problem for you, but in general it's quite convenient.

    One possible way round this would be to use the technique I describe here:
    Aggregating the Result of an MDX Calculation Using Scoped Assignments | Chris Webb's BI Blog

    You could scope your calculation at the date level and it I think it should aggregate up to give you the results you expect.

    Chris

  5. #5
    New Member
    Join Date
    Jul 2015
    Posts
    19
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: OLAP subtotals including filtered members

    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.

  6. #6
    New Member
    Join Date
    Sep 2014
    Location
    Amersham, UK
    Posts
    27
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: OLAP subtotals including filtered members

    No problem, happy to help!

    Chris

  7. #7
    New Member
    Join Date
    Jul 2015
    Posts
    19
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: OLAP subtotals including filtered members

    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

  8. #8
    New Member
    Join Date
    Sep 2014
    Location
    Amersham, UK
    Posts
    27
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: OLAP subtotals including filtered members


  9. #9
    New Member
    Join Date
    Jul 2015
    Posts
    19
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: OLAP subtotals including filtered members

    That's exactly what I was trying ... I will have to read carefully all your blog posts

    Thks again!

  10. #10
    New Member
    Join Date
    Jul 2015
    Posts
    19
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: OLAP subtotals including filtered members

    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!

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •