7 Replies Latest reply on Jan 30, 2017 6:10 AM by Norbert Maijoor

    Calculate a delta (value of current week minus value of last week)

    laura.vanzutphen
    laura.vanzutphen Jan 26, 2017 5:47 AM

      Hi all,

       

      I am trying to calculate a delta: a certain value (savingsrate) of the current week minus this value of last week.

      However, as Tableau does not recognize the WHERE function, I am struggling to create this delta-field.

       

      My data looks as follows:

       

      Date                  Bank                 Rate

      1/1/2017          Bank A               0.1%

      1/8/2017          Bank A               0.2%

      1/15/2017        Bank A               0.2%

      1/1/2017          Bank B               0.3%

      1/8/2017          Bank B               0.3%

      1/15/2017        Bank B               0.3%

      1/1/2017          Bank C               0.2%

      1/8/2017          Bank C               0.2%

      1/15/2017        Bank C               0.1%

       

      Now I would like to calculate a running delta, so suppose today is 1/8/2017, then I would like to have the difference between today's rate and last week's rate by bank. For instance for Bank A, the delta = +0.1%.

      Next week I would like to see the next delta, so the difference between 1/15 and 1/8 (for instance for Bank C this delta = -0.1%).

      Does anyone know how I can create this calculated field?

       

      I have tried something like this

      (IF [Weeknumber]=[Current week] THEN [Rate] END) - (IF [Weeknumber]=[Current week]-1 THEN [Rate] END)

       

      However, this results in NULL, as this field is NULL for the first part if it is NOT the current week and it results in NULL for the second part if it is NOT last week (e.g. current week).

       

      Who can help me with this?