Post reply

Topcount across 2 Dimensions

  • July 10, 2019 at 8:46 am

    #3661474

    Ahoi,

     

    i have the following MDX Skript:

     CREATE SESSION DYNAMIC SET CURRENTCUBE.[Top 10 Material Bestandswert]
     AS TOPCOUNT( [Materialwerksdaten].[Material Key].Members, 11, [Measures].[Bestandswert]), DISPLAY_FOLDER = 'Top Material';

    It works to get the top 10 Material, the problem is that for the same Material multiple Customers exist. I basically need the top 10 not only from Material, but from the combination of MATERIAL and CUSTOMER:

    Dimension attributes:

    [Materialwerksdaten].[Material Key]

    [Kunde].[Kunde ID]?

     

    Measure :

    [Measures].[Bestandswert]

     

    Here is the result/problem(Screenshot/Attachment):

    - on the very left we have the result of the top 10

    - here is the correct Material, but for the Material it shows 0, because theres other Customers with the same Material

    --> i need to get rid of the 0 column, because the 0 obviously isnt part of the top 10 and

    --> i tried to load zeros as nulls into the cube and set the measure to preserve but that didnt do anything

    Unbenannt

     

    • This topic was modified 5 years, 5 months ago by  ktflash.
    • This topic was modified 5 years, 5 months ago by  ktflash. Reason: added result/problem

    Attachments:
    You must be logged in to view attached files.

    I want to be the very best
    Like no one ever was

    https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help

  • July 10, 2019 at 11:36 am

    #3661516

    The simplest way to do a "top 10 by customer" type thing is to use Generate to iterate over each one and perform the TopCount. The below uses this method in the AdventureWorks cube, looking at Top 10 Products per Reseller:

    WITH SET [Top 10 Product by Reseller]
    AS
    GENERATE( // Iterate over the Reseller
    [Reseller].[Reseller].[Reseller].MEMBERS,

    NONEMPTY // Filter out nulls
    (TOPCOUNT // Top count of products
    (
    ([Reseller].[Reseller].CURRENTMEMBER, // Include the current member to diplay, joined to the current iteration
    [Product].[Product].[Product]), // Display the product
    10, // Number for TopCount
    [Measures].[Internet Order Count]) //Measure
    ))

     

    As for your Null/0 issue, that's definitely something to do with your design but it's difficult to say what without looking at your solution in full. You could use the FILTER function to return the set when measure > 0 but it's probably best to fix the design.

    I'm on LinkedIn

  • July 11, 2019 at 7:34 am

    This was removed by the editor as SPAM

  • July 11, 2019 at 8:25 am

    #3661870

    Too much Edit in quick time caused to delete my comment, sorry for that

     

    What kind of design question is it, sorry not sure?

    It's 2 attributes of 2 seperate Dimensions, the connection between the 2 Dimension comes through the Measure as ive learned. The Measuregroup is connected to both Dimensions. Since the Measuregroup references both Customers for same Material it get this combination, the value for this Measure is 0/NULL but it exists for a different Measure within this Measuregroup.

     

    Ive bene trying to adept the Filter u suggested but i am pretty inexperience in MDX.

    Looking at what u have posted i was expecting this adaption of what you posted here to be it, but it produces garbage results. My assumption was that if i leave out the top part i get the combination of Material + Customer in one, sinc the orginal version goes back to FILTER only Material which will end up the same if i add the Customer Dimension in Excel.

    CREATE SESSION DYNAMIC SET CURRENTCUBE.[Top 10 Material Bestandswert]
    ASNONEMPTY // Filter out nulls
    (TOPCOUNT // Top count of products
    (
    ([Materialwerksdaten].[Material Key].CURRENTMEMBER, // Include the current member to diplay, joined to the current iteration
    [Kunde].[Kunde ID].[Kunde ID]), // Display the product
    10, // Number for TopCount
    [Measures].[Bestandswert]) //Measure
    )), 
    DISPLAY_FOLDER = 'Top Material';

    I want to be the very best
    Like no one ever was

    https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help

  • July 11, 2019 at 10:05 am

    #3661886

    You need the GENERATE function if you want to show the top ten products by customer. You have taken this out which is why your results are "garbage".

    For the "0" issue, instead of using a filter in your set or changing your design, if you never want to see a "0" for your measure you could add the following at the start of your MDX script (after CALCULATE;):

    SCOPE ([Measures].[Bestandswert]);
        THIS =
            IIF([Measures].[Bestandswert] = 0, NULL, [Measures].[Bestandswert]);
    END SCOPE;

     

    I'm on LinkedIn

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply