1 Reply Latest reply on Oct 20, 2016 3:08 AM by Chris Geatch

    using traffic light formatting with date and integer fields

    Julie Goodman

      Hi there everyone, this is my first time using the community portal, forgive me if this is not normal questioning.


      I am trying to replicate an excel report to Tableau. 


      The Excel report shows client ID's down rows, with documents to be competed for each client across columns.


      There are columns with date fields to show 0/1 if the client case has been open for 1 or more months, 2 or

      more months, 4 or more months and 7 more months; date fields which documents are due at.


      Beside the 0/1 vlookup field of whether the document exists or not, the document fields are replicated, with

      traffic light conditional formatting to show :

      1. the document is complete, due or not - green

      2. the document is not complete, but not yet due - yellow

      3. the document is not complete, and is due - red


      Sample: https://app.powerbi.com/groups/me/workbooks/1_153075_217050?redirectedFromSignup=1


      In Tableau, I have re-created the date field calculations in an attempt to gain the same outcome - required by management


      I think I am close, but am struggling to concatenate the document integer with the date fields, and colour them.


      Please help if you are able


      Kind regards,


      Julie Goodman

        • 1. Re: using traffic light formatting with date and integer fields
          Chris Geatch


          Welcome to the community portal.  It's only my third time responding, so I'm not quite at the level of expertise of some of the people here, but maybe I can move you in the right direction.  I couldn't quite work out which fields you were trying to work with to determine your traffic light system, as I couldn't get in to see your sample, so I have just used some measures from the Tableau report and, hopefully, you can apply the principle elsewhere.


          You don't need to concatenate all of your fields to use them.  I think your first problem is that your calculated fields are continuous - when that's the case, then Tableau will default to a gradient of colour and it's difficult to apply the effect you want to achieve.  When a numeric field is discrete (or you're using a text field), then you are allowed to use more than one field to calculate the colour palette.


          In the image below, I've used the same 5 dimensions as in your workbook, but I have converted your Health Prompt, MDS Intake and Ramp measures to be discrete (in the Measures card, go to the dropdown for the field, and click on Convert to Discrete).  Drag those three measures onto the marks card, and you'll now find that you can select the Colour label for each of them (to the left of the measure, where the 3 colour labels are in the image below, will be blank, click in the blank space to select the colour option).  You will then be shown the card below that, with four default colours in it.  If you click on the dropdown for that card, there is an 'Edit colours' option, if you go into that you can categorise each combination of values as red, amber or green.  For me, this works well when you have a small number of measures.  If you have more, then you would be better off creating one calculated measure from them and creating a colour key from that.  Just make sure your calculated field is discrete.


          I don't think I've quite answered your question, but hopefully you can use the principle I've described to get the result you want.