7 Replies Latest reply on Nov 8, 2018 7:58 AM by Paul Wachtler

# Calculation to count difference in 2 rows (kinda)

I need to count the number of times dates are the same but product changed.

Wish I could get this to work but  can't compare Sting and integer error:

IF [Service Date]=[Date] AND LOOKUP(ATTR([Product]),-1) != ATTR([Product])

THEN 1

ELSE 0

END

Need to know if "Product" changed on "Service Date".

• ###### 1. Re: Calculation to count difference in 2 rows (kinda)

Hello Jean,

Looking at the sample data the below calumniation is good enough:

IF [Service Date]=[Date] THEN 1 ELSE 0 END

Can you attach the sample data to understand the requirement better.

S

• ###### 2. Re: Calculation to count difference in 2 rows (kinda)

Thanks Kumar.

I updated the post to better explain my request.

I Need to know if "Product" changed on "Service Date" by "Account".

• ###### 3. Re: Calculation to count difference in 2 rows (kinda)

Hi Jean,

You're really close with your current calc.  You just need to convert the date values to strings with the str() function to make that error go away.

Update your calc to this:

IF str([Service Date]) = str([Date]) AND LOOKUP(ATTR([Product]),-1) != ATTR([Product])

THEN 1

ELSE 0

END

Best,

Paul

• ###### 4. Re: Calculation to count difference in 2 rows (kinda)

I'm probably bringing great shame to my family's name right now, but I can't figure this out. What am I doing wrong.

• ###### 5. Re: Calculation to count difference in 2 rows (kinda)

Hahaha no shame involved!  Tableau has some weird default behavior that you just need to code around.

In this case it's saying that the Product metrics you're using in your Lookup calc are aggregated, while the date fields aren't. Just need to add the attr() wrapper around the dates and it should fix it:

IF str(attr(Service Date])) = str(attr([Date])) AND LOOKUP(ATTR([Product]),-1) != ATTR([Product])

THEN 1

ELSE 0

END

• ###### 6. Re: Calculation to count difference in 2 rows (kinda)

YOU ARE THE MAN!!!!!!!   THANK YOU!!!!!!! THANK YOU!!!!!!! THANK YOU!!!!!!!

• ###### 7. Re: Calculation to count difference in 2 rows (kinda)

You're very welcome!  If you don't mind, can you click the "Yes" next to "This Helped Me" on my response?  I'm trying to level up my forum status   Thanks!