2 Replies Latest reply on Dec 15, 2016 5:38 AM by Cristiano Nardocci

# Customers purchasing after a # of consecutive months of not buying

Hi Community,

I'm trying to use the data in our sales db to plot by month the number of customers that are purchasing again after a certain number of consecutive months (I'd like to check 6 or 12). I attach data sample.

Given the data structure I planned to start by pivoting the data, but then I'm not sure how to get a dimension or measure that can allow me to identify the customers that in any specific month have purchased something after let's say 6 consecutive months without buying that product.

Any help would be greatly appreciated!!!!

thanks!!!

• ###### 1. Re: Customers purchasing after a # of consecutive months of not buying

Hi Christiano

Find my approach as reference below and stored in attached workbook version 9.3

Upfront "Pivot" first...

a. Month edit

if [Month]="Jan" then 1

elseif [Month]="Feb" then 2

elseif [Month]="Mar" then 3

elseif [Month]="Apr" then 4

elseif [Month]="May" then 5

elseif [Month]="Jun" then 6

elseif [Month]="Jul" then 7

elseif [Month]="Aug" then 8

elseif [Month]="Sep" then 9

elseif [Month]="Oct" then 10

elseif [Month]="Nov" then 11

elseif [Month]="Dec" then 12

END

b. Month value: if [Pivot Field Values]>0 then [Month edit] END

c. Max Month: zn({fixed [Customer]: max([month value])})

d. Consecutive Month without..: 12-[Max Month]

• ###### 2. Re: Customers purchasing after a # of consecutive months of not buying

Thanks a lot Norbert!

I understand the approach now to get a count of the number of months without purchasing from december. Anyway how would you then be able to have for each month the number of customers that have purchased something but had not purchased anything for the 6 months before? Ideally I'd like to have a line graph with the months on the X axis and the number of customers buying something in that month after at least 6 months without purchasing on the Y axis. Even better it'd be a bubble chart same as the line chart but with the size of the bubbles = the volume corresponding to the customers buying something in that month after at least 6 months without purchasing.