MDX – Counting Consecutive Days

A recent blog post (here) questioned a method for returning the most consecutive days that a customer has brought product for. For example, given a particular customer what’s the largest continuous date span of their purchases? More realistically, a business question may be ‘what are the customers who purchase for more than 5 days in a row’?

In addition to the answer provided in the post, this blog provides a little more support to the solution. To make the data more accessible, the question is based on [Adventure Works] and considers months instead of days (ie, for a customer, what’s the most number of months they have continuously purchased for?). We are using the [Date].[Calendar] hierarchy and the [Internet Sales Amount] measure.

Approach

This approach looks at the problem in two ways. Firstly, we define a measure that can be applied to a specific period that counts the number of periods that have had (continuous) sales. Secondly, we apply this to all periods to determine the largest number of consecutive periods.

Monthly Continuous Periods

If we consider a single customer and their monthly sales (as below), the continuous sales for any month end on the first subsequent month of null sales. For example; the [July 2007] continuous periods end in [August 2007] and [October 2007] end in [December 2007]. The end of the continuous period is defined as the first null in the set which starts in the next month and on the last month.

The number of continuous periods for a month is also the number of members in the set which range from the first period until (but not including) the first (null) period.

select
[Measures].[Internet Sales Amount]
on 0
,[Date].[Calendar].[Month]
on 1
from    [Adventure Works]
where
(
   [Customer].[Customer].&[12301]
)

The end of the continuous period (or the first null member) is satisfied by the expression;

filter( {[Date].[Calendar].currentmember.nextmember : null }
,[Measures].[Internet Sales Amount] = 0
).item(0)
// note that we are only interested in the first null item (and use the .item(0) function to retireive this

The query above can be extended to show the ending month (name) of the continuous period;

with
member [Measures].[end of range member] as
filter(
{[Date].[Calendar].currentmember.nextmember : null }
,[Measures].[Internet Sales Amount] = 0
).item(0)
.member_name
select
[Measures].[Internet Sales Amount]
,[Measures].[end of range member]
}
on 0
,[Date].[Calendar].[Month]
on 1
from    [Adventure Works]
where
(
    [Customer].[Customer].&[12301]
)

Additionally, since a continuous period cannot exist when the current month has no sales, months without sales can be excluded from the calculation (they will be null).

with
member [Measures].[end of range member] as
iif ( [Measures].[Internet Sales Amount] = 0
       , null
       , filter( {[Date].[Calendar].currentmember.nextmember : null }
                   ,[Measures].[Internet Sales Amount] = 0).item(0)
                   .member_name
)

Given the end period (note that we know the start since it’s a relative period), we can specify the continuous range between the start and the end. This set includes an additional member (the last one is not really needed because its value is null), and so it can be excluded from the set. Although it can be excluded using the .prevmeber function, it can be left in the set and removed from the count. The set is simply defined within the iif statement;

with
member [Measures].[end of range member] as
iif ( [Measures].[Internet Sales Amount] = 0
, null
,         filter( {[Date].[Calendar].currentmember.nextmember : null }
,[Measures].[Internet Sales Amount] = 0).item(0)
 .member_name
)
// inclusive
member [Measures].[set (inclusive)] as
iif ( [Measures].[Internet Sales Amount] = 0
        , null
        , settostr(
                  [Date].[Calendar].currentmember :
filter( {[Date].[Calendar].currentmember.nextmember : null }
,[Measures].[Internet Sales Amount] = 0).item(0)
)                   )

// count (just reduce 1)
member [Measures].[set count] as
iif ( [Measures].[Internet Sales Amount] = 0
      , null
 ,   {
 [Date].[Calendar].currentmember :
            filter( {[Date].[Calendar].currentmember.nextmember : null }
    ,[Measures].[Internet Sales Amount] = 0).item(0)
}.count-1
      )

select
{
[Measures].[Internet Sales Amount]
,[Measures].[end of range member]
, [Measures].[set (inclusive)]
, [Measures].[set count]
on 0,

[Date].[Calendar].[Month] on 1
from    [Adventure Works]
where
(
[Customer].[Customer].&[12301]
  )

The Largest Number of Consecutive Periods

Having defined the number of consecutive periods for relative periods, we can define the maximum value for all periods using the max function. Simply specify range set (first argument of max) as the period members. Our query becomes;

with member [Measures].[Range Count] as
iif ( [Measures].[Internet Sales Amount] = 0
        , null
, {[Date].[Calendar].currentmember :
filter( {[Date].[Calendar].currentmember.nextmember : null }
,[Measures].[Internet Sales Amount] = 0).item(0)
}.count-1
    )
member [Measures].[Max Periods] as
max([Date].[Calendar].[Month], [Measures].[Range Count])
select [Measures].[Max Periods] on 0
from    [Adventure Works]
where
(    [Customer].[Customer].&[12301]   )

Other Considerations

The query searches for continuous periods until the last date period (regardless of year). Often, with this type of situation, comparatives are required on an annual basis and so there would be a need to alter the ‘end’ date to be the last data for the current year.

There are also some performance improvements that can be applied to the query; However, I think that this demonstrates the method rather simply and performance addressed as needed.