3 Replies Latest reply on Jun 8, 2016 1:50 PM by Trevor Richardson

    Trying to calculate the minimum date for each id in a column. And format

    Trevor Richardson

      I am trying to calculate the minimum date for a column called finishDate for each every id in another column called ID. I can write the following function MIN([finishDate]) as Calculation1 but now if I put this field into column I cannot use the DATEPART('quarter', [Calculation1]). It will not let me use a calculation like this. I want to be able to use DATEPART to be able to change from quarter to year and so on with a filter real time. Can anyone help me figure out how to show a count of one for the minimum date by id for finish times that are different by row. And then how to filter them by quarter, year, so on. THX! Really confused why the functions will not work together. my Calculation1 should have dates for each id that is the first or minimum date. and then I should be able to count the number for each quarter.

        • 1. Re: Trying to calculate the minimum date for each id in a column. And format
          Ivan Young

          Hi Trevor,

          To calculate the min date for each ID you can create an LOD.  { Fixed [ID] : MIN([finishDate] }, you can then extract a datepart from the LOD you just created.  Let me know if you have any questions.

           

          Good luck,
          Ivan

          2 of 2 people found this helpful
          • 2. Re: Trying to calculate the minimum date for each id in a column. And format
            Simon Runc

            hi Trevor,

             

            So Tableau has several different calculation types (row level, aggregate, table calculation, LoDs). For Aggregate (as your MIN([finishDate]) is), and Table Calculation the VizLoD (Viz Level of Detail) determines their result (eg. SUM([Sales]) in superstore, if you just bring this onto the canvas it shows the value for all the Sales, if you then drag Region (adding this to the VizLoD) it now shows the SUM([Sales]) for each region. I refer to the these as 'on canvas' calculations. If you have ID in your VizLoD, then your calc works fine, but you want it at row level so you can use it 'off canvas' (i.e. as a dimension). This is why FIXED LoD were invented! they let you specify the LoD of a calculation, but return the results to every row (of the specified LoD) 'off canvas'...That the 2 min version of calcs!! (I'd highly recommend watching the On-demand calculation videos - short, free and good!)

             

            So for your example we can create what you want with a FIXED LoD

            [Min Date per ID]

            {FIXED [ID]: MIN([finishDate])}

             

            This is calculated at ID level, and returned at that level, so you can use in a non-aggregated calculation (such as DATEPART)

             

            Hope that does the trick, and sheds some light on calculations in Tableau?

            2 of 2 people found this helpful
            • 3. Re: Trying to calculate the minimum date for each id in a column. And format
              Trevor Richardson

              Thank you so much both of you this was so helpful!