4 Replies Latest reply on Apr 27, 2012 2:58 AM by Jennifer Chin

    First date to meet threshold

    Jennifer Chin

      I'm trying to find the first date a sales threshold is met and show that for each product...

      Capture.PNG

      For instance for Amaretto I don't want a date to show in min date...as the threshold is never met, while I want Cafe Latte to show 1 April 2010 as that is the first day it met it's threshold, etc, etc, etc.  I also want this date to show the actual threshold date no matter what date filters I put in place.  While it might seem easier to just include this in initial data pull, the actual dataset I am using would require joining a table that has millions of rows back onto itself to find this...which I find is slowing the datapull to a crawl...so was hoping to find a tableau calculation to handle this instead...

       

      Thanks all!!!

        • 1. Re: First date to meet threshold
          Fionnuala Gibney

          Hi there,

           

          I used a calculated field to achieve this, do the following:

          WINDOW_MIN(IF [Sum Sales > 1500] THEN Min([Date]) ELSE NULL END)

           

          This uses your boolean check to see if the sales value was passed and pulls that minimum date, and then the window_min pulls the smallest of these as a date the threshold was passed.

           

          I've got a 'Null' in the field when the threshold was not met, not sure if there's an easy way to hide that though.

           

          If you want to convert the date into a string, and replace NULL with blank, then you can do this instead in the calculation:

          IIF(ISNULL(

          WINDOW_MIN(IF [Sum Sales > 1500] THEN Min([Date]) ELSE NULL END)

          ),

          "",

          STR(WINDOW_MIN(IF [Sum Sales > 1500] THEN Min([Date]) ELSE NULL END)))

           

          It checks if the resulting value is NULL, and if so it replaces with "" (empty string), or else puts out the original value.

           

          Take a look and see if that suits, there may be better ways though.

           

          Good luck!

          Fionnuala

          1 of 1 people found this helpful
          • 2. Re: First date to meet threshold
            Jennifer Chin

            promising but the threshold met date changes if date filters change that exclude the first threshold met date

            • 3. Re: First date to meet threshold
              Richard Leeke

              The trick is to express the filter in a way that causes all of the data to be returned from the data source, so that it is available for the table calculation, but then is applied to the results before they are displayed.

               

              The way to do that is to define a calculated field using the LOOKUP() table calculation function, with an offset value of 0. That basically means look up the value of the field in the current row, which just returns the value itself. But defining a filter on that causes the filter to be evaluated after other table calculations.

               

              If you look in the attached I've created the calculation that Fionnuala suggested for finding the first date, and also a field [Date Filter] = LOOKUP(ATTR([DATE], 0) to use for the filter.