2 Replies Latest reply on Apr 16, 2016 11:19 AM by Dana Withers

    Traffic Light KPI - multiple conditions driven by date

    Christi Kurihara

      I am trying to create a traffic light KPI indicator  but being new to Tableau I am encountering some trouble:

      - First, the KPI has to depend on 4 separate date fields in my server...if any of the 4 dates is 0-4 weeks from today then it is green, 4-12 weeks is yellow, and over 12 is red.  Dates in the future, null, or placeholders all have to be red as well.

      - Second, quite unfortunately 1 of the date fields in my server contains nulls (Review Date) and 2 of them have placeholder dates of 1/1/1900 (RFP and Submission Date) both of which I have to work around.

       

      So far I have built a very lengthy if/then statement using ifnull and datediff but it is messy and I am certain that there is a better way to do it that I just haven't found yet.  Additionally, I am uncertain how to build a 3 condition if/then to support it (I know how to in excel, but have quickly learned that excel logic doesn't translate to tableau, at least not 1 to 1).

       

      Below is the first part of the formula I have built so far...I then repeat the same block 2 more times with the count of days for < 12 weeks and > 12 weeks.

       

       

      IF IFNULL(DATEDIFF('day',[RFP Date],today()),-1) <=28

       

      or IFNULL(DATEDIFF('day',[Submission Date],today()),-1) <=28

       

      or IFNULL(DATEDIFF('day',[Review Date],today()),-1) <=28

       

       

      then "Green"

        • 1. Re: Traffic Light KPI - multiple conditions driven by date
          Esther Aller

          Hey Christi,

           

          The exact syntax of calculations is highly dependent on the structure of the data and sometimes how the view is built. If you are able to share a packaged workbook I would be able to give specific advice on writing the calculation.

           

          The excerpt of your calculation looks fine (sometimes they do get quite complicated). I am not entirely sure what you mean by building a 3 condition IF/THEN statement as your current IF/THEN statement has 3 conditions.

           

           

          1 of 1 people found this helpful
          • 2. Re: Traffic Light KPI - multiple conditions driven by date
            Dana Withers

            Hi Christi,

             

            Please see workbook attached.

            I agree with Esther that your calculation looks fine and that sometimes calculations can get quite complicated, but often it depends on how you build it and if there is some clever rules you can apply.

             

            In the attached workbook I've used three fields to calculate this. You can combine them in the same calculation if you wish, but I usually try and keep things as clear as possible even if that means more fields. I've also grouped your rules a bit:

             

            Rule 1: If there is a place holder (01-01-1900) or an empty date, the traffic light should be red...

            So I have created a "Oldest Date" that calculates the oldest of the three dates involved... and puts in a 01-01-1900 if there is a NULL. (If any calculation contains a null as one of the parts, the whole thing becomes null, so that helps).

             

            Rule 2: Aside from rule 1, the traffic light should change dependent on if any of the three days falls within the range.

            So for that I've created a Days Since Most Recent Date that uses the MIN function to get the most recent date and then calculates the date difference between that most recent date and today. (In case you're not looking at this any time soon, I've put in field today that makes sure the calculation stays working correctly but this should be replaced with the TODAY() function.)

             

            Combine the lot: If any line matches rule 1 the light should be red, otherwise the light should be dependent on the number of days.

            So to do the final step to combine it all I've created the Traffic Light calculated field.

             

            I wouldn't exactly say that what I've done is easier than what you were writing, but it might be more easy to read and quicker to type or amend.

             

            I hope that helps you further!

             

            Dana

            1 of 1 people found this helpful