14 Replies Latest reply on May 24, 2017 6:22 PM by Ivan Young

    How do I create a calculated field that finds the first date where a defined threshold is exceeded (v 8.2)

    Liam Welford

      Hi,

       

      I am brand new to Tableau (using v 8.2 as that is the latest I can get through IT) and trying to create some dashboards to replace Excel files we use at the moment. I am aware that Tableau works in a very different way to Excel but I think what I am trying to achieve is reasonable, but have been unable to find out how to do it.

       

      I have a data source which details Schedule date, Region, Points Allocated and Points Loaded. The Points loaded represent minutes of available resource to cover installation works by technicians. The Points Allocated represents minutes of worked already booked.

       

      I have created a calculated field which takes Points booked from Points Allocated to represent the available quota points. "Sheet 1" shows a view created with this data showing the available points.

       

      What I want to create is a calculated field that represents the lead time for each Region, the count of days until a threshold (which I have yet to determine) is exceeded. For example if I set the threshold at 1000 points, the lead-time in Andover would be 5 days, the first available date which exceed 1000 points is the 23rd. Ideally I would like to be able to return both the first date that exceeds the threshold, and number of days until that date, though I assume that by calculating either one of these would enable me to derive the other from it.

       

      Another measure I would like to add is the first date where the Available as a percentage of the Loaded exceeds a threshold, i.e. first date with more than 20% of loaded.

       

      Any help on achieving the above, especially the first point would be gratefully received, I have spent hours reading forum posts, working with a colleague who knows Tableau much better than me, we tried all sorts of things with no luck. We got as far as a calculated field that used an if statement to return a 1 if the threshold was exceeded and a 0 if not, but couldn't work out how to then count the 0's and stop once it got the first 1. I appreciate I could calculate this in the database and bring it in a separate data source I could link by region, but this data has everything I need to calculate the answer.

       

      Edit: Added version I am using!