3 Replies Latest reply on Mar 19, 2018 5:48 AM by Jim Dehner

    Problem with Date Different Calculation

    Wui Heng Goh

      Hi,

       

      I would like someone to advice me what are wrong with my Tableau calculation.

       

      Attached, please find my tableau workbook (ver 10.2 and 10.5) and an excel file.

       

      In the Excel file

      Column A (Patient NRIC) - Patient Identification

      Column E (Collection Date) - Uric Acid Test Performed Date

      Column F (Short Text) - Lab Test Name

      Column G (Test Result) - Uric Acid Test Outcome

      Column H (BaseCase) - Index Case, ie. The first time patient who had Uric Acid test perform

       

      AIM : To calculate the next Uric Acid Test performed and calculate the proportion of patients who have their Uric Acid check in the next 6 months

       

      I set up a calculated new variable 'UA_Test_Day's in Tableau to calculate the next Uric Acid Test with reference to the index case date. Index case is referred to the first time when Uric Acid is performed. Please see below screenshot.

       

      UA_Test_Day_Formula.jpg

      Table Calculation.jpg

      and the partial output as follow :

       

      Calculation Error.jpg

      The first red box is reference to Patient A2262152K. The UA_Test_Days for Uric Acid performed on 31/08/2017 was calculated wrongly.

      The second red box is reference to Patient A5952195M. The UA_Test_Days for Uric Acid performed on 20/04/2015, 07/07/2015 and 13/08/2015 were calculated wrongly.

       

      The BaseCase (Index Care - 1st time Uric Acid Test performed) will have NULL on UA_Test_Days.

       

      Can someone assist me? Why some of the days are calculated wrongly?

       

      Thank you.

        • 1. Re: Problem with Date Different Calculation
          Jim Dehner

          good morning

          I am confused on your expectations -

          your formula is comparing each date to the one before it - is that what you wanted or did you want to compare each date to the first date for each patient

          if it is the second the formula would be

                              DATEDIFF('day',  window_min(MIN([Collection Date])),LOOKUP(MIN([Collection Date]), 0))

           

          and will return this

           

           

          your workbook is attached

          Jim

          If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

          • 2. Re: Problem with Date Different Calculation
            Wui Heng Goh

            Thanks Jim. The formula works well for me.

             

             

                 DATEDIFF('day',  window_min(MIN([Collection Date])),LOOKUP(MIN([Collection Date]), 0))

             

            Can you let me know how to use "window_min" and the lookup with offset 0?

             

            Thank you.

            • 3. Re: Problem with Date Different Calculation
              Jim Dehner

              the 2 formulas are table calculations - windowmin looks at the whats in the viz and takes the min value but bear in mind that it is taking the min of the aggregate value in the expression - that aggregate can be any function sum(), avg(), min(), max() etc

              Lookup with 0 just takes the value of the current position in the table - think like lookup , -1 goes back 1 position - lookup 0 is just the current position

               

              Jim

              If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.