9 Replies Latest reply on Oct 11, 2018 10:05 AM by Tony Fahd

How to identify members year over year across 3 years?

Hi so I am attaching a workbook that has my customers and their encounters over the years (2016, 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 2016, customers who had cases in all 3 years.

Thanks for the help

• 1. Re: How to identify members year over year across 3 years?

Hi Tony,

Are you looking for something like this:

1. Year Occurrence per customer = { FIXED [Customer]: COUNTD([Year])}

2. occurrence = if [Year Occurrence per customer]=1 then "Only "+[Year]  elseif [Year Occurrence per customer]=3 then "All three Years" END

• 2. Re: How to identify members year over year across 3 years?

yes this is great. what about if i want to add 3 other occurrences: one would be members in 2016 and 2017, members in 2017 and 2018 and members in 2016 and 2018

• 3. Re: How to identify members year over year across 3 years?

ELSEIF [Year] = "2016" AND  [Year] = "2017" THEN  "Two Years" something to that effect

• 4. Re: How to identify members year over year across 3 years?

You can edit the logic as below:

if [Year Occurrence per customer ]=1 then "Only "+[Year]

elseif [Year Occurrence per customer ]=2 then "Two Years"

elseif [Year Occurrence per customer ]=3 then "All three Years" END

• 5. Re: How to identify members year over year across 3 years?

Thanks Meenu! one last question, anyway i can separate the Two years into 3 buckets(2016 and 2017),(2016 and 2018) and (2017 and 2018).

Thanks for the help, this is great

• 6. Re: How to identify members year over year across 3 years?

Hi Tony,

• 7. Re: How to identify members year over year across 3 years?

Thanks Meenu! one last question, anyway i can separate the Two years into 3 buckets(2016 and 2017),(2016 and 2018) and (2017 and 2018).

Thanks for the help, this is great

• 8. Re: How to identify members year over year across 3 years?

Hello Tony,

You can tweak the logic as shown below:

occurrence  =

if [Year Occurrence per customer]=1 then "Only "+[Year]

elseif [Year Occurrence per customer]=3 then "All three Years"

elseif [Year Occurrence per customer]=2 then

{FIXED [Customer]:MAX([Year])} +' and ' + {FIXED [Customer]:MIN([Year])}

END

1 of 1 people found this helpful
• 9. Re: How to identify members year over year across 3 years?

Meenu, this is works great. Thanks!!

Tony