4 Replies Latest reply on Jun 20, 2018 7:27 AM by Gerardo Varela

# Calculation help - Lookup and LOD

Friends I am in need of urgent help-

Requirement  - Below is a customer , ordering items via multiple Agents.

While Customer is buying an item(say, 56) , We need to track the change/shift of Agent

Eg -

Question 1 -  We need to track the Agent shift date.

Customer ID=1,

Item number = 56

Agent Flow => Agent A to Agent B

Initially, Customer ID 1 started ordering items for Agent A, the later on 4/24/2015, he ordered same item for Agent B.

We need to track the Agent shift date.

Question 2 -  In addition to question 1, we need to track when Customer moved back to previous agent or Did multiple Agent shifts?

Customer ID=1,

Item number = 785

Agent Flow => Agent C to Agent D to Agent C

 Customer ID Item number Agent Name Transaction date Agent Change Date 1 56 Agent A 2/27/2015 1 56 Agent A 3/27/2015 1 56 Agent B 4/24/2015 4/24/2015 1 56 Agent B 6/26/2015 1 785 Agent C 11/28/2014 1 785 Agent C 12/26/2014 1 785 Agent D 2/27/2015 2/27/2015 1 785 Agent C 4/22/2016 4/22/2016

I tried using Lookup function as below - Lookup(ATTR(Customer ID),-1) . I am getting results, but lookup is working for across table, not pane (i mean not for individual 'item number') wise.

Is there any way that we can create LOD in combination with Lookup?

Please me in creating a calculation.."Agent Change Date"

thanks

Keerthi

• ###### 1. Re: Calculation help - Lookup and LOD

You might have better luck doing this directly in SQL if that is where your data lies, you can use the lag function to get what you are looking for.

• ###### 2. Re: Calculation help - Lookup and LOD

This can be done in Tableau but it isn't easy.  I started by adding a unique identifier to your data:

Then a lookup calculcation to see if the previous item number is the same as the current one:

IF

LOOKUP(ATTR([Item number]),0)=LOOKUP(ATTR([Item number]),-1)

THEN "Y"

ELSE

"N"

END

Then another lookup calculation to check toe see if the previous agentname is the same as the current one:

IF

LOOKUP(ATTR([Agent Name]),0)=LOOKUP(ATTR([Agent Name]),-1)

THEN "Y"

ELSE

"N"

END

Then a third to identify what we need.

[First]= "Y" AND [Second] = "N"

All the above table calculations are set to compute on Table Down. This answers the immediate question but any further questions would complicate this even more.  See attached workbook 10.5.

Regards,

Gerardo

• ###### 3. Re: Calculation help - Lookup and LOD

Thanks a lot GERARDO VARELA .

This works .....

• ###### 4. Re: Calculation help - Lookup and LOD

You're welcome!

Regards,

Gerardo