5 Replies Latest reply on Oct 5, 2018 1:58 PM by Shinichiro Murakami

# How to identify customers year over year?

Hi so I am attaching a workbook that has my customers and their encounters over the years (2017 and 2018). i like to create a field that identify whether a customer has only cases in 2017, customers who have only cases in 2018 and customers who had cases in both years.

so for example, i like to create a field saying that customer A appeared in both years, Customer B and D appeared only in 2018, Customer C appeared only in 2017.

any help is appreciated!

• ###### 1. Re: How to identify customers year over year?

HI Tony

You can use LOD and two layers of calc.

Overview: Level of Detail Expressions

Thanks,

Sihn

2 of 2 people found this helpful
• ###### 2. Re: How to identify customers year over year?

Hello Tony,

You can try below approach:

1. Min year = { FIXED [Customer]:MIN([Year])}

2. num of years? = {FIXED [Customer]:COUNTD([Year])}

3. Check=

if [num of years?]<>1 THEN "Both Years"

ELSEif [Min year]='2017' and [num of years?]=1 THEN "Only 2017"

ELSEIF [Min year]='2018' and [num of years?]=1 THEN "Only 2018"

END

1 of 1 people found this helpful
• ###### 3. Re: How to identify customers year over year?

Shin, this is exactly what I wanted. Thanks a bunch!

Tony

• ###### 4. Re: How to identify customers year over year?

Hi Meenu, this is works great

• ###### 5. Re: How to identify customers year over year?

Hi Tony

You are welcome.

Shin