3 Replies Latest reply on Feb 23, 2018 12:24 PM by Norbert Maijoor

# Trying to capture existing customers vs new customers over 2 periods

Hi, this might be a simple question but for some reason i cant figure it out. I have a simple table like this, in Q4 customer 5 is a new customer and customer 1 disappears:

 Customer Quarter 1 Q3 1 Q3 2 Q3 2 Q3 2 Q3 3 Q3 4 Q3 4 Q3 2 Q4 2 Q4 2 Q4 3 Q4 4 Q4 4 Q4 5 Q4 5 Q4

How can i create a filter to automatically catch customer 5 (and all others in a big dataset) so i can use it as a filter? In my dataset i'm always only comparing 2 quarters which are selected with a parameter.

Thanks!

• ###### 1. Re: Trying to capture existing customers vs new customers over 2 periods

Hi Robert,

Find my approach as reference below and stored in attached workbook version 10.3 located in the original thread.

1. NM D1. First Quarter Customer: {fixed[Customer]:min([Quarter])}

2. NM D2. Last Quarter Customer: {fixed[Customer]:max([Quarter])}

3. NM D3. Last Quarter : {fixed:max([Quarter])}

4. NM D4. Display: if [NM D1. First Quarter Customer ]=[NM D3. Last Quarter ] then "New Customer"

elseif ([NM D1. First Quarter Customer ]<>[NM D3. Last Quarter ] and [NM D2. Last Quarter Customer]=[NM D3. Last Quarter ]) then "Existing Customer"

elseif [NM D2. Last Quarter Customer]<>[NM D3. Last Quarter ] then "Lost Customer"

END

5. Drag the required objects to the indicated locations, show filter NM D4. Display & untick Show Header for Customer in the Rows

Regards,

Norbert

1 of 1 people found this helpful
• ###### 2. Re: Trying to capture existing customers vs new customers over 2 periods

Thank you Norbert!!!! That worked perfectly, you're the man!!!!!!!

• ###### 3. Re: Trying to capture existing customers vs new customers over 2 periods

Hi Robert,

You are welcome. Thanks for the "Badge". Appreciated:)

Regards,

Norbert