locked
MDX - get "new" data RRS feed

  • Question

  • Hello,

    I have a request for a query that will bring back all "new" client revenue.  A new client is defined as:

    - Having no revenue for 5 years prior to the first date of revenue

    My starting query is the following - any help on how to filter the data with the above criteria would be greatly appreciated!

    Select NON EMPTY  { [measures].[Revenue] } on columns,
    NON EMPTY { ( [Date].[Financial Year].[Financial Year], [Client].[Client].[Client]) } on rows
    FROM 
    Financials
    

    Thanks very much.

    Wednesday, May 10, 2017 6:43 PM

Answers

  • If - and it is a big IF - you can use the 5 years prior to the reporting period (2008:2012 in your example) as the base for checking all clients' 'newness', then the code is relatively straightforward, as in this AW example (it uses 2 years because AW is not big enough):
    WITH 
      SET Yrs AS 
        [Date].[Calendar].[Calendar Year] 
      MEMBER [Measures].CurrentPeriodCount AS 
        NonEmpty
        (
          [Customer].[Customer].[Customer]
         ,(
            Yrs
           ,[Measures].[Internet Sales Amount]
          )
        ).Count 
      MEMBER Measures.CurrentPeriodNewCount AS 
        Except
        (
          NonEmpty
          (
            [Customer].[Customer].[Customer]
           ,(
              Yrs
             ,[Measures].[Internet Sales Amount]
            )
          )
         ,NonEmpty
          (
            [Customer].[Customer].[Customer]
           ,(
              LastPeriods
              (2
               ,Yrs.Item(0).Item(0).PrevMember
              )
             ,[Measures].[Internet Sales Amount]
            )
          )
        ).Count 
    SELECT 
      {
        [Measures].CurrentPeriodCount
       ,[Measures].CurrentPeriodNewCount
      } ON 0
    FROM 
    (
      SELECT 
          [Date].[Calendar].[Calendar Year].&[2013]
        : 
          [Date].[Calendar].[Calendar Year].&[2014] ON 0
      FROM [Adventure Works]
    );
    Much easier than calculating 5 years back from each customer's individual first revenue date and checking its revenue for that period, both in MDX and for the server to calculate.

    Expect me to help you solve your problems, not to solve your problems for you.

    • Marked as answer by hhewson Friday, May 12, 2017 6:19 PM
    Thursday, May 11, 2017 3:23 PM
  • Hi Hillary,

    Thanks for your response.

    Please replace [measures].[Revenue] with measure group name which contain [measures].[Revenue] . Such as "Intenet Sales" in cube AdventureWorks.

    WITH SET NEWCLIENT AS
    EXCEPT
    ([Client].[Client].[Client],
             EXISTS
    ([Client].[Client].[Client],
                   
    (nonempty( [Date].[Financial Year].[Financial Year], [measures].[Revenue] )).item(0):
                   
    (nonempty( [Date].[Financial Year].[Financial Year], [measures].[Revenue] )).item(0).LAG(-4),
                    "Measure group name for
    [measures].[Revenue]")
            
    )
    SELECT
    [measures].[Revenue] on 0,
           NEWCLIENT on
    1
    FROM
    [Financials]

    For more information, please refer to below link:

    https://docs.microsoft.com/en-us/sql/mdx/exists-mdx


    Best Regards
    Willson Yuan
    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, May 12, 2017 3:58 AM

All replies

  • Hi Hhewson,

    Thanks for your quesiton.

    Firstly, we need to get the first [Financial Year] with revenue. please use Nonempty function to get all the [Financial Year] which having revenue, item(0) to get the first [Financial Year] contain revenue.

    (nonempty( [Date].[Financial Year].[Financial Year], [measures].[Revenue] )).item(0)

    Get that 5 [Financial Year] using MDX LAG function.

    (nonempty( [Date].[Financial Year].[Financial Year], [measures].[Revenue] )).item(0):(nonempty( [Date].[Financial Year].[Financial Year], [measures].[Revenue] )).item(0).LAG(-4)

    then use MDX function EXISTS to get the clients which having revenue for  5 years prior to the first date of revenue.

    EXISTS([Client].[Client].[Client],
             (nonempty( [Date].[Financial Year].[Financial Year], [measures].[Revenue] )).item(0):
             (nonempty( [Date].[Financial Year].[Financial Year], [measures].[Revenue] )).item(0).LAG(-4),
             [measures].[Revenue])

    After that, we can use EXCEPT function to get all the new client.
     EXCEPT([Client].[Client].[Client],
             EXISTS([Client].[Client].[Client],
                    (nonempty( [Date].[Financial Year].[Financial Year], [measures].[Revenue] )).item(0):
                    (nonempty( [Date].[Financial Year].[Financial Year], [measures].[Revenue] )).item(0).LAG(-4),
                    [measures].[Revenue])
             )

    In the end, we can use below MDX query to get the desired results:

    WITH SET NEWCLIENT AS
    EXCEPT([Client].[Client].[Client],
             EXISTS([Client].[Client].[Client],
                    (nonempty( [Date].[Financial Year].[Financial Year], [measures].[Revenue] )).item(0):
                    (nonempty( [Date].[Financial Year].[Financial Year], [measures].[Revenue] )).item(0).LAG(-4),
                    [measures].[Revenue])
             )
    SELECT [measures].[Revenue] on 0,
           NEWCLIENT on 1
    FROM 
    [Financials]
    WITH SET NEWCLIENT AS
    EXCEPT([Client].[Client].[Client],
             EXISTING([Client].[Client].[Client],
                    (nonempty( [Date].[Financial Year].[Financial Year], [measures].[Revenue] )).item(0):
                    (nonempty( [Date].[Financial Year].[Financial Year], [measures].[Revenue] )).item(0).LAG(-4),
                    [measures].[Revenue])
             )
    SELECT [measures].[Revenue] on 0,
           NEWCLIENT on 1
    FROM 
    [Financials]


    Best Regards
    Willson Yuan
    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





    Thursday, May 11, 2017 1:52 AM
  • @willson: did you perhaps mean Exists and not Existing? With the current syntax, the two sets provided to the Except function have different dimensionality.

    @hhewson: I feel a more precise definition of a 'new' client is required. Technically speaking, there is no revenue prior to the first - ever, not just for 5 years. So it would seem that the query algorithm should start with a reporting period as user input, then you get clients with revenues in this period AND their first revenue dates in this period, then for each such client you check whether there were any sales for 5 prior years from this client's revenue date (a bit tricky with exclusive/inclusive ranges here) and exclude the clients with such sales. MDX will differ depending on how the reporting period is defined in the query: if slicer axis/subquery, then you can use a dynamic set; if you could have many such periods on rows/columns, you cannot.


    Expect me to help you solve your problems, not to solve your problems for you.

    Thursday, May 11, 2017 7:58 AM
  • Hi Alexei,

    Thanks for pointing it out, I have updated it in my original reply.


    Best Regards
    Willson Yuan
    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

    Thursday, May 11, 2017 8:23 AM
  • Hi Willson,

    Thanks so much for the great explanation.  I'm getting an odd error when I run the suggested query:  

    Executing the query ...
    The 6277288571.734 measure group was not found.
    Run complete

    Any ideas about this?

    Thanks again,

    Hillary

    Thursday, May 11, 2017 1:14 PM
  • Thanks very much, Alexei.  You are very correct - I left out the detail about our reporting period.  We filter the query on the last 5 years of data, so in this case it reports data from 2013-2017.  We detect when there is "new" revenue in this period and then report the revenue going forward.  Thanks for pointing that out.  This would be more in line with our report.  We pull the date range dynamically depending on a date parameter.

    Select NON EMPTY  { [measures].[Revenue] } on columns,
    NON EMPTY { ( [Date].[Financial Year].[Financial Year], [Client].[Client].[Client]) } on rows
    FROM 
    (Select {[Date].[Financial Year].&[2013] : [Date].[Financial Year].&[2017]}
    ON COLUMNS
    FROM 
    Financials)

    Thanks,

    Hillary

    Thursday, May 11, 2017 1:21 PM
  • If - and it is a big IF - you can use the 5 years prior to the reporting period (2008:2012 in your example) as the base for checking all clients' 'newness', then the code is relatively straightforward, as in this AW example (it uses 2 years because AW is not big enough):
    WITH 
      SET Yrs AS 
        [Date].[Calendar].[Calendar Year] 
      MEMBER [Measures].CurrentPeriodCount AS 
        NonEmpty
        (
          [Customer].[Customer].[Customer]
         ,(
            Yrs
           ,[Measures].[Internet Sales Amount]
          )
        ).Count 
      MEMBER Measures.CurrentPeriodNewCount AS 
        Except
        (
          NonEmpty
          (
            [Customer].[Customer].[Customer]
           ,(
              Yrs
             ,[Measures].[Internet Sales Amount]
            )
          )
         ,NonEmpty
          (
            [Customer].[Customer].[Customer]
           ,(
              LastPeriods
              (2
               ,Yrs.Item(0).Item(0).PrevMember
              )
             ,[Measures].[Internet Sales Amount]
            )
          )
        ).Count 
    SELECT 
      {
        [Measures].CurrentPeriodCount
       ,[Measures].CurrentPeriodNewCount
      } ON 0
    FROM 
    (
      SELECT 
          [Date].[Calendar].[Calendar Year].&[2013]
        : 
          [Date].[Calendar].[Calendar Year].&[2014] ON 0
      FROM [Adventure Works]
    );
    Much easier than calculating 5 years back from each customer's individual first revenue date and checking its revenue for that period, both in MDX and for the server to calculate.

    Expect me to help you solve your problems, not to solve your problems for you.

    • Marked as answer by hhewson Friday, May 12, 2017 6:19 PM
    Thursday, May 11, 2017 3:23 PM
  • Hi Hillary,

    Thanks for your response.

    Please replace [measures].[Revenue] with measure group name which contain [measures].[Revenue] . Such as "Intenet Sales" in cube AdventureWorks.

    WITH SET NEWCLIENT AS
    EXCEPT
    ([Client].[Client].[Client],
             EXISTS
    ([Client].[Client].[Client],
                   
    (nonempty( [Date].[Financial Year].[Financial Year], [measures].[Revenue] )).item(0):
                   
    (nonempty( [Date].[Financial Year].[Financial Year], [measures].[Revenue] )).item(0).LAG(-4),
                    "Measure group name for
    [measures].[Revenue]")
            
    )
    SELECT
    [measures].[Revenue] on 0,
           NEWCLIENT on
    1
    FROM
    [Financials]

    For more information, please refer to below link:

    https://docs.microsoft.com/en-us/sql/mdx/exists-mdx


    Best Regards
    Willson Yuan
    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, May 12, 2017 3:58 AM
  • Thanks Willson!  I believe I am now getting the proper results.  I really appreciate your help on this!

    Hillary

    Friday, May 12, 2017 6:19 PM
  • Thanks Alexei.  I'll play around with this idea as well.  I appreciate your help!

    Hillary

    Friday, May 12, 2017 6:27 PM