4 Replies Latest reply on Jul 2, 2018 9:58 AM by Jim Dehner

# Lookup calculation not working as intended

Hi my excel file is set up as so:

Date
Acknowledged
09-Jun90100
10-Jun95100
11-Jun92100

I created a calculated field "Effectiveness" which is simply sum([acknowledged])/sum(([received]) and then added a filter for the date range. (So 90% for June 9, 95% for June 10, 92% for June 11)

I wanted to create a calculated field for my trend arrow (green up arrow if better than previous day, red if down, etc). However it is not working as intended. Currently it keeps returning null according to the legend. I already assigned up, no change, and down arrows based on hard values.

My formula is:

IF ZN([Acq - Effectiveness]) - LOOKUP(ZN([Acq - Effectiveness]), -1) > 0 THEN "UP"

ELSEIF ZN([Acq - Effectiveness]) - LOOKUP(ZN([Acq - Effectiveness]), -1) = 0 THEN "NO CHANGE"

ELSEIF ZN([Acq - Effectiveness]) - LOOKUP(ZN([Acq - Effectiveness]), -1) < 0 THEN "DOWN"

END

What is my issue?

• ###### 1. Re: Lookup calculation not working as intended

Is that what you're looking for?

Little change in KPI calculation

[Ratio]

[KPI]

IF ZN([Ratio]) - LOOKUP(ZN([Ratio]), -1)< 0 THEN "DOWN"

ELSEIF ZN([Ratio]) - LOOKUP(ZN([Ratio]), -1) = 0 THEN "NO CHANGE"

ELSEIF ZN([Ratio]) - LOOKUP(ZN([Ratio]), -1) > 0 THEN "UP"

ELSE ""

END

workbook v10.5 attached for your reference.

Mahfooj

• ###### 2. Re: Lookup calculation not working as intended

Hi,

Thanks for the reply. It wouldn't let me open the workbook since it's a newer version of Tableau. However, I changed the calculation per your recommendation but it is still returning null or in your case "".

• ###### 3. Re: Lookup calculation not working as intended

To give an update in my question, it is still open. The formula described above MIGHT work. I put some of the dimensions into a text table and saw that the dimensions were being returned corresponding to a Null date and that it was using the wrong column for my calculations. However I cannot find any errors in my spreadsheet

• ###### 4. Re: Lookup calculation not working as intended

Hi

See the attached

Your formula works just fine - you were getting a Null on the first value because the lookup( value, -1)  didn-t have a value

just for demonstration I added a date with an equal ACQ value and a check for the first value

it returns this

to create the viz I just put you formula on the shape and on rows (calculated down) and included the measures

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.