The technique is nested IF (or case) statements... Think of them like a decision tree...
So it would end up being something like this - we use the ELSE part to do more tests
IF [A] = [B] THEN 'GREEN'
ELSEIF [B] < ([A]+14) THEN 'AMBER'
ELSEIF [B] > ([A]+14) THEN 'RED'
ELSE NULL END
Note - You can either return 0, 0.5 and 1 and swap the colors, or return the colors as text (as I have above), or as Norbert has done have the labels say something more informative (above target etc.)
The below is exactly the as the above, just the output values are different
IF [A] = [B] THEN 0
ELSEIF [B] < ([A]+14) THEN 0.5
ELSEIF [B] > ([A]+14) THEN 1
ELSE NULL END
I'm not entirely following your logic, but I hope the above gets you close enough to work out how to amend it... if not let me know.
You need to make sure the date type of fields A and B are dates.
Nice work Norbert
I think (and Jennifer may correct me) but rather than using today's date the required logic was between 2 source dates (A+B)
I like the way you defined only 2 'tests' with the third derived by not being the other 2, good work, I like that!
I hope between our 2 answers Jennifer has enough IF logic to work out what she needs...
@Jennifer - there are lots of resources about logical calculations, this is a good place to start >> Understanding Logical Calculations | Tableau Software
Like I said earlier, if you imagine it as a decision tree
and then each ELSEIF is another branch... this is a nice example...
If you have any further questions let us know!
Thanks for the hint;) Reading carefully the initial post is key.... I am aware, folks always "complain' I am in the fast lane;)
So I went back did it over again but still "assuming" Current date=Current date today() and not dinamicly defined based e.g. range
Interesting to see what the "verdict" will be;)
Thank you for your inputs.
Norbert, I am unable to open your example, i'm on Tableau Desktop 9..
That is correct Mark, do require the light to be formatted by colour according to two source dates, not the date of today.
I've inputted this formula...
IF [Project Execution Completion (Proposed)]=[Project Execution Completion (Current)] THEN '0'
ELSEIF [Project Execution Completion (Proposed)]<([Project Execution Completion (Current)] +14) THEN '0.5'
ELSEIF [Project Execution Completion (Proposed)]>([Project Execution Completion (Proposed)] +14) THEN '1'
ELSE NULL END
Formula makes logical sense to me.. and i've edited around it.. but I still get "error" when i try to input.
Any idea's whats wrong with it?
Good morning Jennifer!
On what version are you exactly...9....?
The verdict is yours;)
The formula looks OK, I wonder if its the formats of the dimensions which is causing the problem.
Are Project Execution Completion (Proposed) and Project Execution Completion (Current) dates? (they need to be...)
They should look like this
I just tried swapping the dates for Order and Ship Dates in the Superstore data and get no errors
Mark, yes they are in date format.. could it be American/Vs USA way.. they are in DD/MM/YY..?
Norbert, I really like this view as well.. I'm going to use it in table format for another dashboard as part of my drill downs to individual project information.
I love it! " When a plan comes together":)