3 Replies Latest reply on Apr 19, 2018 8:41 AM by Shinichiro Murakami

    Calculated field to retrieve value from another row in datasource (conditional maximum)

    Ryan Blosser

      Hello,

       

      The topic isn't worded really well, so I figured more explanation is necessary. v10.5 by the way.

       

      I have a datasource that looks like this:

        

      ProductDate
      a1/1/2018
      a1/3/2018
      a1/9/2018
      a1/21/2018
      b1/1/2018
      b1/3/2018
      b1/9/2018
      b1/21/2018
      b1/22/2018

       

      What I want is a field that, for each product, retrieves the maximum value of the date that is at least one week before the current row. Note that Ranker (in my workbook) might not (probably will not) exist in the data set, that was a helper column.

       

      So in my head, I am looking at a row, filtering the datasource for rows where the Product matches the one in the current row AND the Date at least 7 days before, then getting the Max date of what is left.

         

      ProductDateexpected result
      a1/1/2018NULL
      a1/3/2018NULL
      a1/9/20181/1/2018
      a1/21/20181/9/2018
      b1/1/2018NULL
      b1/3/2018NULL
      b1/9/20181/1/2018
      b1/21/20181/9/2018
      b1/22/20181/9/2018

       

       

      note that it is NOT simply the row before, because the date cannot be within 6 or fewer days than the current one.  Also, I understand that I can do this outside of Tableau, but I want to learn more data manipulation inside of it.

       

      Thanks for your help.