none
MDX Cumulative Count - With Repeating Numbers? RRS feed

  • Question

  • Hi

    I am trying to build a cumulative count, however it is returning a value where it should not?

    In the screenshot below you will see that the 'green' cells are the only results I want to see - but for some reason when my original measure moves to another category Plants to Animals - the cumulative measure is still counting an entry for the previous category Plants? How can I change this MDX to only return a result where the dimension category is the same as the previous / last known category?

    CREATE MEMBER CURRENTCUBE.[Measures].[Count]
     AS 
    
    SUM({[Period].CurrentMember.Level.Members}.Item(0):
    [Period].CurrentMember,[Measures].[Contract Count])
    , 
    VISIBLE = 1;


    I.W Coetzer

    Friday, August 16, 2019 6:21 AM

Answers

All replies

  • Hi I.W Coetzer,

    Could you please show us the attribute structures of the dimension "Period"? And what attribute is for the values "Plants","Animals"? Are the attribute "category", "YearMonth" combined in one hierarchy?

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, August 16, 2019 7:29 AM
  • Hi Will

    No they are separate, here is my structure.

    P.S this is related to the previous question that you answered - but now I want to include another dimension:


    I.W Coetzer

    Friday, August 16, 2019 8:10 AM
  • Hi I.W Coetzer,

    I need to see the attributes under dimension table "Period" that you are using, so that I could know which attribute column should be used. Have you created hierarchies under that dimension table?

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, August 16, 2019 8:20 AM
  • Hi

    No hierarchies.

    I am actually using the PeriodKey since it contains the unique periods that I am working with:

    201901

    201902

    201903

    201904 

    etc.


    I.W Coetzer


    • Edited by I.W Coetzer Friday, August 16, 2019 8:45 AM
    Friday, August 16, 2019 8:43 AM
  • Hi

    No hierarchies.

    I am actually using the PeriodKey since it contains the unique periods that I am working with:

    201901

    201902

    201903

    201904 

    etc.


    I.W Coetzer

    So how do you display the values "Plants","animals" ? do they belong to attributes of another dimension table?

    Or you could use MDX query to display results above to answer my question.

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, August 16, 2019 8:50 AM
  • Hi

    Yes, plants and animals are Category Names - thus coming from another dimension.

    For now I do this

    1) I use Contract Key to simply filter on one contract - for example purposes

    2) I use Period to display in the Rows

    3) Now I am also trying to show the Category Name

       - but this does not make sense currently since it is repeating the cumulative count for each category - even though the contract did not belong to all categories?


    I.W Coetzer

    Friday, August 16, 2019 9:09 AM
  • Hi I.W Coetzer,

    Thanks for your detailed explanation.

    Per your replies, the mdx query is probably like this.

    CREATE MEMBER CURRENTCUBE.[Measures].[Count]
     AS 
    SUM(
    [Period].[Period].currentmember*({[Category].CurrentMember.Level.Members}.Item(0)):
    [Period].[Period].currentmember*[Category].CurrentMember,[Measures].[Contract Count])
    , 
    VISIBLE = 1;

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, August 16, 2019 9:23 AM
  • Hi

    Thank you - I tried this but now I am getting all possible category names (i have a few hundred) appearing for every period and the #VALUE! printed for this measure for all possible combinations of period and category name 


    I.W Coetzer

    Friday, August 16, 2019 10:00 AM
  • I tweaked your MDX a bit and the answer is 'almost' what i am looking for:

    CREATE MEMBER CURRENTCUBE.[Measures].[CountNew2]
     AS
    SUM
    (
        (
            {[Period].CurrentMember.Level.Members}.Item(0):
            [Period].CurrentMember
        )
        * [Company].[Legal Owner].CurrentMember
    ,[Measures].[Contract Count])
    , 
    VISIBLE = 1  ; 

    Except the orange lines should not be appearing ;-(


    I.W Coetzer

    Friday, August 16, 2019 10:22 AM
  • Hi I.W Coetzer,

    Could you please show us the query results by executing the following query? I need to adjust the query based on your output data.

    SELECT 
    [Measures].[Contract Count] ON 0,
    [Period].[Period].[Period]*[Company].[Legal Owner].[Legal Owner] ON 1
    FROM YOUR_CUBE

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, August 19, 2019 9:40 AM
  • Hi,

    Please see results below - i filtered on one loan for this example.

    As per previous the one loan was in company XYZ in 2019/1 and during 2019/03 it was moved to company ZZZ.

    I need a count for each Period in the correct company  - if an entry is missing the mdx calculated measure should indicate a count for the each period - for example 2019/02 should have a count of 1 next to the company that it belonged to in the pas - thus 2019/02 - Company XYZ should have a count of 1 and it currently holds null since there are no measures in that period.


    I.W Coetzer

    Tuesday, August 20, 2019 7:58 AM

  • I.W Coetzer

    Tuesday, August 20, 2019 8:00 AM
  • Hi I.W Coetzer,

    Thanks for your reply.

    It is strange that your sample data is always changing. So how can I get your real thought from the changing sample data? From your reply, the expected output is not Cumulative Count, obviously far from the original logic.

    From sample data, you provided the sample data with four columns. But with the execution of my query, it is impossible to get four columns. For your expected output, there is  no indication that you need to get a cumulative count under specific attribute "Period". How can I go on with such case?

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, August 21, 2019 6:49 AM
  • Hi

    Okay, let me try again - I myself am struggling to explain this from an MDX point of view so I will not.


    I.W Coetzer

    Wednesday, August 21, 2019 8:22 AM
  • Hi, I have re-created the cube so that it is easier to prototype a working solution.

    Here is the dimensional model, dimension usage etc.



    I.W Coetzer


    • Edited by I.W Coetzer Wednesday, August 21, 2019 9:21 AM
    Wednesday, August 21, 2019 9:13 AM
  • Sample of my attempt at an MDX calculation to produce my desired results ...

    not working out ..

    CALCULATE; 
    CREATE MEMBER CURRENTCUBE.[Measures].[CountV1]
     AS 
    SUM
    (
        (
            {[Period].CurrentMember.Level.Members}.Item(0):
            [Period].CurrentMember
        )
        * [Category].[Category Name].CurrentMember
    ,[Measures].[Count])
    , 
    VISIBLE = 1  ; 


    Here is the measure group records again - for clarification


    I.W Coetzer


    • Edited by I.W Coetzer Wednesday, August 21, 2019 9:24 AM
    Wednesday, August 21, 2019 9:22 AM

  • I.W Coetzer

    Wednesday, August 21, 2019 9:24 AM
  • Hi I.W Coetzer,

    This problem has been solved in https://social.msdn.microsoft.com/Forums/sqlserver/en-US/b66c540f-befd-4d6a-8e31-90c8551bb301/mdx-calculated-measure-to-lookup-previous-periods-count?forum=sqlanalysisservices,

    Please kindly close the thread.

    Thanks for your cooperation.

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by I.W Coetzer Monday, August 26, 2019 7:49 AM
    Friday, August 23, 2019 8:36 AM