Count customers in rolling periods

[Mobax]

Hello,

I am creating a bar chart to count customers by different conditions with Dimension Date.YearMonth and Measures as follows:

Count of active customers in previous 12 months: 

Aggr(RangeSum(Above(Count(distinct Company),0,12)),Date.autoCalendar.YearMonth) - Done

Count of new customers with first sale in last 2 months: Aggr(RangeSum(Above(Count( Aggr(Min([Date.autoCalendar.Date]), Company)),0,2)), Date.autoCalendar.YearMonth) - Done

But I can not figure out how to count the 'Sleeping customers' - those who have sales in last 12 months BUT no sales in last 6 months.

Thanks for your help.

[Chanty4u]

May b try this 

Aggr( Count({$< [Date.autoCalendar.Date] = {"<=$(=Max([Date.autoCalendar.Date])-183)"}, [Company.Sales] = {"<=0"}, [Company.CustomerID] = {"=Count({$< [Date.autoCalendar.Date] = {">=$(=Max([Date.autoCalendar.Date])-365)"} >} [Company.CustomerID])"}>} DISTINCT [Company.CustomerID]), Date.autoCalendar.YearMonth )

[Prem0212]

I think this expression will work for you.

Count({<[Ship Date] = {"<=$(=Max([Ship Date]))"}, [Ship Date] = {">=$(=AddMonths(Max([Ship Date]), -12))"}>} DISTINCT [Customer ID])

Please like and accept the solution if u liked it.

[Mobax]

Thank you for your expression, it looks very suitable for my case. I am trying to make it work for my data model, but I doubt one part of it: The set expression for Company given this way: 

[Company.CustomerID] = {"=Count({$< [Date.autoCalendar.Date] = {">=$(=Max([Date.autoCalendar.Date])-365)"} seems to return a counted number, and I think we need a list of customers. Am I correct ?

[Rohan]

Hi,

I think he meant P() instead of Count().

Thanks & Regards,

Rohan.

[Mobax]

Thank you for your answer, Rohan. I adapted the expression for my data, but I cannot get any value, only '-'. Can you, please have a look at it:

Aggr(Count({$<num([Date.autoCalendar.Date]) = {"<=$(=Max([Date.autoCalendar.Date])-183)"},
Qtty = {"<=0"},Company = {"=P({$< num([Date.autoCalendar.Date]) = {'>=$(=Max([Date.autoCalendar.Date])-365)'} >} Company)"}>}DISTINCT Company), Date.autoCalendar.YearMonth ).

Thanks

[Rohan]

Aggr(Count({$<num([Date.autoCalendar.Date]) = {"<=$(=Max([Date.autoCalendar.Date])-183)"},
Qtty = {"<=0"},Company = P({$< num([Date.autoCalendar.Date]) = {'>=$(=Max([Date.autoCalendar.Date])-365)'} >} Company)>}DISTINCT Company), Date.autoCalendar.YearMonth ).

Thanks & Regards,

Rohan.