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

How to identify members year over year across 3 years?

Tony Fahd Oct 9, 2018 7:51 AM

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

Members year over year.twbx 19.7 KB

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

meenu choudhary Oct 9, 2018 8:00 AM (in response to Tony Fahd)

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
Like Show 0 Likes(0)

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

Tony Fahd Oct 9, 2018 8:39 AM (in response to meenu choudhary)

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
Like Show 0 Likes(0)

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

Kelly McGrady Oct 9, 2018 8:46 AM (in response to Tony Fahd)

ELSEIF [Year] = "2016" AND [Year] = "2017" THEN "Two Years" something to that effect
Like Show 0 Likes(0)

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

meenu choudhary Oct 9, 2018 8:55 AM (in response to Tony Fahd)

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
Like Show 0 Likes(0)

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

Tony Fahd Oct 9, 2018 10:05 AM (in response to meenu choudhary)

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
Like Show 0 Likes(0)

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

meenu choudhary Oct 9, 2018 8:26 PM (in response to Tony Fahd)

Hi Tony,

If your query is resolved, please mark the answer as CORRECT and HELPFUL, so as to close the thread.
Like Show 0 Likes(0)

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

Tony Fahd Oct 10, 2018 10:30 AM (in response to meenu choudhary)

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
Like Show 0 Likes(0)

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

meenu choudhary Oct 11, 2018 4:45 AM (in response to Tony Fahd)

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
Like Show 1 Likes(1)

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

Tony Fahd Oct 11, 2018 10:05 AM (in response to meenu choudhary)

Meenu, this is works great. Thanks!!
Tony
Like Show 0 Likes(0)

Actions