none
MDX - Order count for consecutive days

    Question

  • Hello,

    I have report requirement that need complex MDX query. I need a list of Product which have been order more than two times within consecutive three days (User can change number of continue days from report Parameter). We have following data in Cube.

    ProductID

    SalesDate

    ProductOrderCount

    1234

    04/20/2013

    1

    ABCD

    04/20/2013

    1

    0000

    04/21/2013

    3

    1234

    04/21/2013

    1

    1234

    04/22/2013

    1

    1234

    08/15/2013

    1

    1234

    01/17/2014

    5

     

    If customer select only within three days than report should show two Product and result should be following.

    ProductID

    No.Order

    1234

    3

    0000

    3

    1234

    5

     

    It will give 1234 Product because it has been order consecutive three days (April 20,21,22). It will not count order made on 08/15/2013 because it is not in three days of boundary. Report will also take 0000 Product because it was ordered 3 times on April 21. 1234 Product was ordered again on 01/17/2014 more than multiple times.  I couldn't use Max or Min Date because those wouldn't be bound in consecutive three days always.  

    ProductID = [Product].[ Product]

    ProductOrderCount = [Measures].[ Product Count]

    SalesDate = [Date].[Date]

     

    I don’t know how to define three days of boundary in MDX query.

    Any help would be highly appreciated.

    Thanks and Regards!!!


    • Edited by arpatel Friday, April 11, 2014 11:13 PM
    Friday, April 11, 2014 9:42 PM

All replies

  • what will happen in the following case?! ... how many times the 1234 product will appear?!!

    Saturday, April 12, 2014 8:17 AM
  • Number of Order count not suppose to be overlap. In your case it will give you only 3 rows (A,D and G). Result set would be

    ProductID

    ProductOrderCount

    1234

    4

    1234

    7

    1234

    4

    Cheers,

    Ankit


    • Edited by arpatel Saturday, April 12, 2014 1:10 PM
    Saturday, April 12, 2014 12:58 PM
  • I think it would be much more easier if you add another attribute to your Date dimension to divide your Date attribute to groups of 3 days.... this will be _again_ much easier than achiving this with MDX query
    Saturday, April 12, 2014 2:54 PM
  • I think we have to manage everything in MDX since Group of Days and Number of Order will be derived by customer in report (Report Parameter). I have came up with following query. It is working but not taking care of Overlapping Products as you described in your sample records.

     

     WITH MEMBER Measures.[b] as SUM(LastPeriods (-3,[Date].[Date].CURRENTMEMBER ),[Measures].[Product Count] )

     

    SELECT { [Measures].[Product Count] , Measures.[b]} ON COLUMNS

                ,NONEMPTY(filter(([Product].[ProductID].[ProductID],[Date].[Date].[Date]),measures.[b] > 1), [Measures].[Product Count]) ON ROWS

      FROM [Cube]

    Saturday, April 12, 2014 10:48 PM
  • would you please try this:

    with set [multiple of n] as filter(([Date].[Date].children),[x] =1)

          member [x] as iIf([y]-[z]=0,1,0)

          member [y] as round(day([Date].[Date].currentmember.membervalue)/3)

          member [z] as day([Date].[Date].currentmember.membervalue)/3

          member [b] as SUM(LastPeriods (3,[Date].[Date].currentmember),[Measures].[Product Count])

    select {[Measures].[Product Count],[b]} on 0,

     NONEMPTY(filter(([Product].[ProductID].[ProductID].children, [multiple of n]),measures.[b] > 1), [Measures].[Product Count]) on 1

    FROM [Cube]


    The [X],[Y], and [z] are to find the multiples of 3; so in your report you have to change the 3 there to a parameter. You may find an easier way to find the multiples of a number, but this one should work fine.

    Excuse me if the query contains some syntax errors ... I could not check it.

    -----------------------------------------------------------------------------------------------------------
    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers.


    • Edited by Butmah Sunday, April 13, 2014 8:12 AM
    Sunday, April 13, 2014 8:11 AM
  • I am trying add your logic in my query but I am not getting what I need. May be I need to massage my query.

    Meanwhile I got another idea. I derived one more measure to do counting on reverse direction (Used PrevMember to exclude current member count). By this way you would get Null values for first member of 3 days of group. Now only you have to look for NULL values. But again it will not give Product info if it was ordered continue more than 3 days. for e.g. if Product has been order continue 7 days, this query will not break order in 2 group. it consider one group and give only one record with total of first three days

      WITH MEMBER Measures.[b] as SUM(LastPeriods (-3,[Date].[Date].CURRENTMEMBER ),[Measures].[Product Count] )

             MEMBER Measures.[C] as SUM(LastPeriods (2,[Date].[Date].CURRENTMEMBER.PrevMember ),[Measures].[Product Count] )

     

     

    SELECT { [Measures].[Product Count] , Measures.[b]} ON COLUMNS

     

                ,NONEMPTY(FILTER(([Product].[ProductID].[ProductID],[Date].[Date].[Date]),(Measures.[b] > 1 AND IsEmpty(Measures.[c]))), [Measures].[Product Count]) ON ROWS

     

      FROM [Cube]

    Sunday, April 13, 2014 10:20 PM