3 Replies Latest reply on Oct 23, 2014 10:01 AM by Julie Montemayor

    Calculation to determine minimum date across five expressions for a record

    Julie Montemayor

      I have tried a few things to make this work and cannot get it figured out.

      For each record I have several dates that need to be compared to determine the earliest date of those.

      So far, MIN(exp1,exp2) worked, but with only two values. I then tried to nest those and that did not work.

      I also tried using an IF statement but the calculation was not working and there were to many to compare.

      I believe it should be fairly easy, as in Excel I used SMALL(A1,B1,C1,D1,E1) and the earliest date was populated.

      Any ideas on how to calculate this would be greatly appreciated.

      Thank you!

        • 1. Re: Calculation to determine minimum date across five expressions for a record
          Julie Montemayor

          Not all of the dates are populated sometimes some of them are null, so the MIN(exp1,exp2) nested into another MIN(exp1,exp2) give the value as null. However, the nulls should be ignored and an actual date for the earliest date should be populated.

          Thank you!

          • 2. Re: Calculation to determine minimum date across five expressions for a record
            Matt Lutton

            Hi Julie:

             

            Its very difficult to answer with a text description alone.  Can you post a packaged workbook example and provide some mockups of what you'd like to have as an end result?  You can mock up an example using the Superstore Sales data that ships with Tableau, and post screenshots to communicate what you would like to do with the data.  Alternatively, you can randomize a subset of your actual records in Excel (or your preferred data source), and use that as the basis for your sample packaged workbook for the Forums.

             

            I think I know what you're after, but without an example and visuals its very difficult to know, and I'd just be guessing if I started replying with things to try.  Its much easier to help with an example, and a clear description of what you're trying to accomplish (visual mockups of the expected result are very helpful).

             

            It wounds like you will need to use some sort of Table Calculation like WINDOW_MIN to evaluate the minimum across several dimensions.  But its very difficult to explain how you might do this without having an example to interact with and use as the basis for our demonstration.

             

             

             

            Cheers!

            • 3. Re: Calculation to determine minimum date across five expressions for a record
              Julie Montemayor

              I was able to actually nest this calculation a few times:

              IF NOT ISNULL([Hard Close Date])

              AND NOT ISNULL([MIN 3])

              THEN MIN([MIN 3],[Hard Close Date])

              ELSE IFNULL([Hard Close Date],[MIN 3])

              END

              to get what I needed, although there must be an easier, more efficient way to do this.

              For each of the two dates used I named them MIN 1, MIN 2, MIN 3, and then solved date to find the final earliest date of them all.

              I was having issues as well due to some of them being null, so the result when finding the MIN with two expressions would yield null.

              Thank you!