3 Replies Latest reply on Jun 29, 2018 10:51 AM by Stephen Groff

    Create LOD with MIN DATE

    Stephen Groff

      My current calculation:

       

      Calculation1

      This calculation allows me to see what serial #'s are being recorded on a particular date... for example, it will show me the distinct values overall for today.  Thus far, this data has been accurate.

       

      COUNTD(

      IF CONTAINS([FA Desc],'PDI')

      THEN [Serial #]

      ELSE

      NULL

      END

      )

       

      Trouble is, my overall count is wrong.  When I select year 2018, I should have 1117 distinct serial numbers, but instead it's showing me 1131.

      Upon drilling down my data, for some reason it is counting the serial # twice when it falls on different dates

      For example:  When I filter the drilled down data to June 1st I get serial # 0123-4567, and when I filter to June 10th I get serial# 0123-4567.

       

      Calculation2:

      I need calculation where Tableau will remove the duplicate serial numbers and only keep the first occurrence by date (MIN DATE I assume), then give me that total.

       

      That way I have a calculation that gives me the accurate overall count, and I can use that number in other calculations (i.e.   X / # of Total machines)

       

      I think it's something like:

       

      COUNTD(

      (IF CONTAINS([FA DESC],'PDI') AND { fixed [Serial #]:MIN([Date])}=[Date]

      THEN [Serial #]

      ELSE NULL

      END

      )

       

      However, I am unable to test it because Tableau just sits in "Sending Data to Server".... "Processing Request"... "Completing Request".... I left it like that for over an hour with no change, so I am assuming something is up with my calculation.

      Is there a easier simplified version of the calc I'm trying to create?

       

      Thanks!

        • 1. Re: Create LOD with MIN DATE
          Deepak Rai

          {FIXED:COUNTD(IF{ fixed [Serial #]:MIN([Date])}=[Date]AND CONTAINS([FA DESC],'PDI') THEN Serial # END)}

          1 of 1 people found this helpful
          • 2. Re: Create LOD with MIN DATE
            Bryce Larsen

            Make a new Serial # column that only contains the value if it's the first date of the Serial #:

             

            CF_Serial#:

            {fixed [Serial #], [Date]: MAX(IF [Date] = {fixed [Serial #]: MIN([Date])} THEN [Serial #] END)}

             

            You could then use this new field in your calculation(s) moving forward.

             

            1 of 1 people found this helpful
            • 3. Re: Create LOD with MIN DATE
              Stephen Groff

              For whatever reason I continue to have problems with FIXED calculations as it pertains to any custom SQL query I create out of Oracle.  This is not a Tableau problem but an Oracle problem (actually it probably has something to do with my lack of knowledge of SQL and how Oracle and Tableau work together).

               

              But this solution was the best fit for me.  It didn't work exactly like I had hoped, but I was able to create a work around using this model to get the results I wanted.  Many thanks!