9 Replies Latest reply on Oct 13, 2016 10:55 AM by David Li

# Need help with a table calculation using lookup

I have a data set with the following columns

 id tstamp category id sub category id SeqNum_tstamp 1 1/1/2016 3843 4 1 1 1/1/2016 3742 2 2 1 1/1/2016 3742 3 3 1 1/1/2016 3742 4 4 1 1/1/2016 3742 5 5

I want to add a new column "Hop" using tableau. Basically, the logic is

For the user within a category, the first category they see is the initial start point. I would then expect the sub category to be incremented incrementally. If the order is sequential, the Hop is set to 0 but if I see a non sequential sub category id within the same category, the hop should be set to 0. attaching the excel data

• ###### 1. Re: Need help with a table calculation using lookup

Hi Ujval, try something like this:

IF LOOKUP(MIN([Sub-Category ID]), -1) = MIN([Sub-Category ID]) - 1 THEN PREVIOUS_VALUE(0) + 1

ELSE 0

END

Set this to compute along Sub-Category ID and SeqNum_tstamp.

• ###### 2. Re: Need help with a table calculation using lookup

I tried it but not able to get the desired answer. Would you be able to post a workbook? We can then cross compare what I am seeing vs what you get?

Thanks so much !

Regards,

Ujval

• ###### 3. Re: Need help with a table calculation using lookup

Sorry, that was supposed to be a minus instead of a plus. I've changed it in the previous reply.

IF LOOKUP(MIN([SubCategory ID]), -1) = MIN([SubCategory ID]) - 1 THEN PREVIOUS_VALUE(0) + 1

ELSE 0

END

Tableau 9.3+ workbook attached.

1 of 1 people found this helpful
• ###### 4. Re: Need help with a table calculation using lookup

I dont think I was clear in what I wanted to do

If you see the excel file at the start, the first time the "Hop" happens is row 39.

On Row 27, the user started category id 8470 and sub category id 1 so the hop is 0. On Row 38, the sub category is sequential till 12 and then 39, we see sub category id as 4 for the same 8470 category. This change in order is a Hop that needs to be marked off as a 1

Regards,

Ujval

• ###### 5. Re: Need help with a table calculation using lookup

Ah, yes, that clarifies things. Please see the attached workbook.

I changed the calculation to this:

IF LOOKUP(MIN([Sub Category Id]), -1) != MIN([Sub Category Id]) - 1 THEN 1

ELSE 0

END

This needs to compute along the SeqNum and also Sub-Category ID. Also, SeqNum needs to be put as the second pill in the rows shelf so the rows sort correctly.

• ###### 6. Re: Need help with a table calculation using lookup

This works like a charm !!! I hate to bother you but any way to have this calculated at the actual table level so I can use the Hop* calculations downstream as a ratio or other elements?

Regards,

Ujval

• ###### 7. Re: Need help with a table calculation using lookup

You're welcome! If that's a satisfactory solution, could you please mark it as correct so this no longer shows up as unanswered?

And no, unfortunately, there's no way to calculate this at the record level unless you do it upstream of Tableau (e.g. in Excel). However, you could potentially put other table calculations on top of these.

• ###### 8. Re: Need help with a table calculation using lookup

David is the absolute best !!!

• ###### 9. Re: Need help with a table calculation using lookup

Haha, thanks! Happy I could help!