none
SubTotal, and GrandTotal issue with currentmember, prevmember

    Question

  • Hi there,

    I am having an issue regarding to calculating SubTotals and GrandTotals. They are always displayed even some of the dates are filtered out.

    Here is the statement:

    CREATE MEMBER CURRENTCUBE.[Measures].[Sales_Delta]
     AS CASE WHEN 
    ([Date].[Calendar].CurrentMember, [Measures].[Sales]) = 0 THEN 
    NULL
    ELSE
    ([Date].[Calendar].CurrentMember, [Measures].[Sales]) -
    ([Date].[Calendar].PrevMember, [Measures].[Sales])
    END,
    NON_EMPTY_BEHAVIOR = { Sales }, 
    VISIBLE = 1 ,  DISPLAY_FOLDER = 'Sales' ,  ASSOCIATED_MEASURE_GROUP = 'CompanySales'  ;  

    SCOPE ([Measures].[Sales_Delta], [Date].[Calendar].Level(0));
    THIS=SUM([Date].[Calendar].Children);
    END SCOPE;

    2010

    January 1000

    February 500

    March  1000 (this is removed)

    GrandTotal 3500

    Thanks for any ideas

    Recai


    Sunday, September 01, 2013 4:48 PM

All replies

  • Hi recai ,

    When you're summing all the children for certain level, it will sum all the set .
    The set on the axis will be evaluated once before evaluating the mdx and return values, so we want to have a dynamic set .. use the day/Month or any relevant level .
    Please try something like the below :

    Create DYNAMIC set CURRENTCUBE.[RelevantDates]
    As {Filter([Date].[Calendar].[Day].members
       ,[Date].[Calendar].CURRENTMEMBER)};  
    SCOPE ([Measures].[Sales_Delta], [Date].[Calendar].Level(0));
    THIS=SUM([RelevantDates]);
    END SCOPE;

    Hope it helps :)


    Regards, David .

    Monday, September 02, 2013 8:53 AM
  • Hi David,

    Thanks for the reply, and the code.

    It works, and the sums are showing up nicely, but the problem is when I bring another dimension to the pivot then the sums are getting messed up again. And there are a lot dimensions a user can select.

    So what is happening is this according to above numbers provided when March is filtered out (btw grand total should have been 2500, not 3500 above):

    GrandTotal = 1500 (the total of Jan-Feb)

    2010 total = 2500 (sums all the months including march)




    • Edited by recai Tuesday, September 03, 2013 3:26 AM
    Tuesday, September 03, 2013 1:19 AM
  • Hi recai ,

    Could it be you have some other scopes later at your calculations that run over that one ? so the numbers messed up ..


    Regards, David .

    Tuesday, September 03, 2013 7:27 AM
  • Hi David,

    No, there is not another scope, but here is what i found so far.

    If I create the member like this:

    CREATE MEMBER CURRENTCUBE.[Measures].[Sales_Delta]
     AS 
    IIF([Measures].[Sales], [Date].[Calendar].CURRENTMEMBER.PREVMEMBER) = 0, NULL,
    ([Measures].[Sales]) -
    ([Measures].[Sales], [Date].[Calendar].CURRENTMEMBER.PREVMEMBER)

    )

    Then sub, and grandtotal display correctly along with the scope code you sent. The only issue with that is the negative values are displayed for the for the future months (which there are no sales yet) which make sense because of the nature of the formula, but the client does not want to see those negatives. When I use below 2 statements one with CASE and other with IIF to wrap the above statement out then the negative issue is resolved but sums get messed up again.

    CASE WHEN

    ([Measures].[Sales], [Date].[Calendar].[CurrentMember]) = 0 THEN NULL

    ELSE

    ...ABOVE IIF

    END

    or this

    IIF([Measures].[Sales], [Date].[Calendar].CURRENTMEMBER.PREVMEMBER) = 0 OR [Measures].[Sales], [Date].[Calendar].CurrentMember =0)

    ,NULL .......


    Any ideas?

    Thanks,

    Recai




    • Edited by recai Wednesday, September 04, 2013 5:35 PM typo
    Wednesday, September 04, 2013 1:50 PM
  • Hi recai ,

    Sorry about the delay (holidays .. :) . I was thinking about creating the calculation as a calculated member, check if there is no negative value, and if there is - we'll define it as null . Now the use of NONEMPTY can help .


    Regards, David .

    Sunday, September 08, 2013 8:02 AM