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)

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?

• ###### 1. Re: Calculate a delta (value of current week minus value of last week)

Goedemiddag Laura,

"Ben niet zeker" maar find my approach as reference below and stored in attached workbook version 9.3

RUNNING_SUM(ZN(SUM([Rate])) - LOOKUP(ZN(SUM([Rate])), -1))

• ###### 2. Re: Calculate a delta (value of current week minus value of last week)

Hi Norbert,

Thanks for your suggestion. However, I only need the difference between the current week and the last week.

In your table I see that for Bank A the Delta amounts 0.10% on both 8-1-2017 and 15-1-2017, while it should be 0% at 15-1-2017.

• ###### 3. Re: Calculate a delta (value of current week minus value of last week)

Hi Laura,

No problem then the calculation should be  ZN(SUM([Rate])) - LOOKUP(ZN(SUM([Rate])), -1)

• ###### 4. Re: Calculate a delta (value of current week minus value of last week)

Thanks Norbert, this was exactly what I was looking for!

• ###### 5. Re: Calculate a delta (value of current week minus value of last week)

Laura,

Your are welcome. Thanks for the "Awards". Fijne avond gewenst

• ###### 6. Re: Calculate a delta (value of current week minus value of last week)

Norbert Maijoor

I only want to see the latest data (the current week). If I hide the previous week (by using a filter on the Date-field and set it to MAX) then the delta-field does not work anymore.

Any clue how to solve that?

• ###### 7. Re: Calculate a delta (value of current week minus value of last week)

Goedemiddag Laura,

Find my approach based on two separate calculated fields.

a. Current week: if DATEdiff('week',[Date],today())=0 then [Rate]/100 END

b. Current week -1: if DATEdiff('week',[Date],today())=1 then [Rate]/100 END

c. Delta vs Previous week: sum([Current week -1])-sum([Current week])

1 of 1 people found this helpful