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

    Help: Date Formula

    jennifer brett

      I have a traffic light field, as follows:

       

      TAB pic.PNG

       

      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
          Mark Fraser

          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
            Norbert Maijoor

            Hi Jennifer!

             

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

            traffictwo.png

            • 3. Re: Help: Date Formula
              Mark Fraser

              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
                Norbert Maijoor

                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
                  jennifer brett

                  Hi guys,

                   

                  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?

                   

                  Thanks!

                  • 6. Re: Help: Date Formula
                    Norbert Maijoor

                    Good morning Jennifer!

                     

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

                    • 7. Re: Help: Date Formula
                      Norbert Maijoor

                      Morning Mark!

                       

                      The verdict is yours;)

                      • 8. Re: Help: Date Formula
                        Mark Fraser

                        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
                          Norbert Maijoor

                          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

                           

                           

                          traffic 9.0.png

                          • 10. Re: Help: Date Formula
                            jennifer brett

                            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
                              Norbert Maijoor

                              Jennifer!

                               

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

                              • 12. Re: Help: Date Formula
                                jennifer brett

                                Ok... it works!  Thanks very much guys

                                 

                                 

                                very helpful as always!