12 Replies Latest reply on Dec 16, 2015 6:50 AM by jennifer brett

# Help: Date Formula

I have a traffic light field, as follows:

Which is red, green or yellow according to the following fields: Let call them A, B.

The original data inputted, say A, B.

A= DD/MM/YY   Proposed Date

B=DD/MM/YY    Current Date

I've created a simple mark field, that shows a circle (Scaled 0-1 and removed scale)

What I need now, is a new field, with a formula that creates values 0, 0.5 and 1 in order to determine the color of this circle mark.

0 = If A is = to B   (ON TIME)

0.5 if B > than A but less than two weeks beyond. (i.e. late but by no longer than two weeks).

1 IF B> than two weeks past A. (LATE by MORE than two weeks)

Then, following this, I can assign 0 as GREEN colour, 0.5 as AMBER, and 1 as RED.

This is to determine if a project is late currently with its proposed date, on time, or just a bit late e.g. traffic lighted.

Does anyone know what formula I can use to create this? I dont know the process of formula that tableau will read regarding dates.

I have created ones in the past, where there is just red or green required using formula such as:

IF([Current or A])=([Proposed B]) THEN 0 ELSE 1 END

And assigned 0 as green and 1 as red.

I have then dragged this new field as a condition to the colour of my blank mark.

Any help would be greatly appreciated, I need simple inputting of dates into original data to feed through to create this traffic light system.

Thanks

• ###### 1. Re: Help: Date Formula

Hi Jennifer

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.

Cheers

Mark

• ###### 2. Re: Help: Date Formula

Hi Jennifer!

Fun exercise!:) Find my approach in attached workbook version 9.0

• ###### 3. Re: Help: Date Formula

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!

Cheers

Mark

• ###### 4. Re: Help: Date Formula

Mark!

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;)

• ###### 5. Re: Help: Date Formula

Hi guys,

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?

Thanks!

• ###### 6. Re: Help: Date Formula

Good morning Jennifer!

On what version are you exactly...9....?

• ###### 7. Re: Help: Date Formula

Morning Mark!

The verdict is yours;)

• ###### 8. Re: Help: Date Formula

Hi Jennifer

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

Cheers

Mark

• ###### 9. Re: Help: Date Formula

Jennifer!

Since the "verdict" was at Mark's side I had to do the exercise;)

Attached once again your approach in workbook version 9.0

• ###### 10. Re: Help: Date Formula

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.

• ###### 11. Re: Help: Date Formula

Jennifer!

I love it! " When a plan comes together":)

• ###### 12. Re: Help: Date Formula

Ok... it works!  Thanks very much guys