1 Reply Latest reply on Nov 17, 2016 11:21 PM by Chris Chalmers

    Difference from previous row based on Condition

    Naveen Vanaparthi

      Hi,

      I know Lookup work for the difference

      User wanted to see the difference from previous row if the Date condition is not null.

      Please find the workbook  and screenshot attached. Currently I drawn the data thru excel as desired, but need dynamically thru Tableau.

      For the example of date 6/1/2016 , difference from previous row is 750-250 =500.

      For the example of date 4/1/2016 , previous row is Null ,so want the difference from before previous row i.e.,250-100=100.

        • 1. Re: Difference from previous row based on Condition
          Chris Chalmers

          Hey Naveen,

           

          I'm assuming you need this calculation to work even if there are multiple rows next to each other with null signature dates. With that assumption, I think the following table calc is what you're looking for. Also see the attached workbook.

           

          diff-non-null-calc.PNG

           

          With these table calc settings:

           

          diff-non-null-table-calc-settings.PNG

           

          I added an extra row to your data to test multiple null rows next to each other, and here are the results:

           

          diff-non-null-results.PNG

           

          See attached workbook.

           

           

          Explanation

          The calculation looks deceptively simple, but the logic behind it is a bit technical. Table calcs cannot keep stepping backwards in the partition conditionally. E.g. you can't say "for this row in the partition, use the first row before it for which some condition is true". Instead, they must know how many steps they're going backwards ahead of time. In this case, for each row of each partition we've written the table calc such that it examines the current row and the row immediately before it.

           

          The calculation works by using a known property of the previous row (an invariant) to calculate the current row. By assuming that our calculation works as intended, we can assert the following invariant for the previous row:

           

          [Expected Value Calc] = [Amount] - [Amount (prior non-null signature date)]

           

          We can then rearrange that invariant to determine that, for the previous row:

           

          [Amount (prior non-null signature date)] = [Amount] - [Expected Value Calc]

           

          So if the previous row is null, We can evaluate [Amount] - [Expected Value Calc] for that row to figure out what the [Amount] is for the last row whose signature date is not null. That's what this part of the calculation is doing:

           

          LOOKUP(ATTR([Amount]), -1) - PREVIOUS_VALUE(0)

           

          And the rest of the calculation is built around that.

           

           

          Hope that helps!

          -Chris Chalmers

          1 of 1 people found this helpful