1 2 Previous Next 15 Replies Latest reply on Feb 6, 2017 6:34 PM by Tim Cooper

# Calculate difference between oldest and newest date values in workbook

Hi All,

I'm hoping you can help with this one and that it is relatively easy.

I am trying to build a calculation where I can show the difference in value between the oldest date and newest date on a workbook I have.

The calculation is to show the difference in Amps being used by a component.

So the oldest date on my board is 08/01/2016 and the reading value [Amps] is 1122

the newest date is 18/03/2016 and the reading value [Amps] is 1158

So the value I would expect to see is -36

Please be aware that the date and value will change on a weekly basis so next date will be 25/03/2016 and a new value. Ideally I need the calculation to be able to cope with the newest date and value changing every week. The oldest value will always remain as 08/01/2016 and reading of 1122.

Also if possible it would be great to get an exact % difference returned too - so for the example above it would be a minus % difference.

Is there an easy way to do this via a calculated field?

• ###### 1. Re: Calculate difference between oldest and newest date values in workbook

What about something like this:

IF [CalendarDate] = {MIN([CalendarDate])} THEN [Sales] END

-

IF [CalendarDate] = {MAX([CalendarDate])} THEN [Sales] END

Hope this helps!

- Derrick

• ###### 2. Re: Calculate difference between oldest and newest date values in workbook

Hi Derrick

Thanks for taking the time to answer my questions. Unfortunately the calculation you have sent returns null values when applied.

• ###### 3. Re: Calculate difference between oldest and newest date values in workbook

Hi Tony,

Do you happen to have a sample workbook along with the desired result to help the community better understand the question?

• ###### 4. Re: Calculate difference between oldest and newest date values in workbook

Hey Tony,

Do you have filters applied or something that would change the min or max date?

If so, we'll need to add it to the LoD calc.

IF [CalendarDate] = {FIXED [myFilterField1], [myFilterField2] : MIN([CalendarDate])} THEN [Sales] END

-

IF [CalendarDate] = {FIXED [myFilterField1], [myFilterField2] : MAX([CalendarDate])} THEN [Sales] END

• ###### 5. Re: Calculate difference between oldest and newest date values in workbook

Hi Nicholas Certainly - here is the workbook as a sample.

As you will see on the workbook the first date is 08/01/2016 and always will be. I'm looking for something that does a subtraction on the newest value in the workbook (in this case the value on 18/03/2016) and the value on 08/01/2016.

Ideally would like to achieve amount and % difference.

• ###### 6. Re: Calculate difference between oldest and newest date values in workbook

Hi Derrick thanks for getting back to me.

I don't think I have any filters applied in relation to date. However I'm doing a SUM on the reading part of the workbook - would this affect your query?

• ###### 7. Re: Calculate difference between oldest and newest date values in workbook

Hi Tony,

Have you tried using a Percent Difference Quick table calculation?

1. Right-click on Sum(Reading)

2. Select Quick Table Calculation> Percent Difference

3. Right-click on Sum(Reading)

4. Select Relative To>First

5. Format your numbers and labels: Formatting Numbers and Dates | Tableau Software

1 of 1 people found this helpful
• ###### 8. Re: Calculate difference between oldest and newest date values in workbook

Ah, the workbook helps.

Here you go:

{FIXED [Room Location], [MEASURE] : SUM(IF [DATE] = {FIXED [Room Location], [MEASURE] : MIN([DATE])} THEN [READING] END)}

-

{FIXED [Room Location], [MEASURE] : SUM(IF [DATE] = {FIXED [Room Location], [MEASURE] : MAX([DATE])} THEN [READING] END)}

• ###### 9. Re: Calculate difference between oldest and newest date values in workbook

Derrick that is awesome! Thank you very much for your help.

Now I'm pushing it a bit but have you any idea how to display those differences as a % too? (If you can work that one out I'll buy you a beer or two if our paths ever cross!)

• ###### 10. Re: Calculate difference between oldest and newest date values in workbook

You should be able to use the same concept, but different math. It should be something like this right?

(

// Start - Finish

{FIXED [Room Location], [MEASURE] : SUM(IF [DATE] = {FIXED [Room Location], [MEASURE] : MIN([DATE])} THEN [READING] END)}

-

{FIXED [Room Location], [MEASURE] : SUM(IF [DATE] = {FIXED [Room Location], [MEASURE] : MAX([DATE])} THEN [READING] END)}

)

/

// Divided by Start

{FIXED [Room Location], [MEASURE] : SUM(IF [DATE] = {FIXED [Room Location], [MEASURE] : MIN([DATE])} THEN [READING] END)}

1 of 1 people found this helpful
• ###### 11. Re: Calculate difference between oldest and newest date values in workbook

Superb Derrick!

Thank you so much for your help today. The beers are definitely on me!

• ###### 12. Re: Calculate difference between oldest and newest date values in workbook

Hi Tony

Derrick's method should work, but it will depend on your workbook/ setup.

I have made something in 9.2.6 (attached) which hopefully provides some help.

Here is a screenshot

These give us our start and end points... they are dynamic, and will change with the data

WIN_MIN = WINDOW_MIN(MIN([Date]))

WIN_MAX = WINDOW_MAX(MAX([Date]))

Next their associated values

MIN_VALUE = IF ATTR([Date]) = [WIN_MIN] THEN ATTR([Value]) END

MAX_VALUE = IF ATTR([Date]) = [WIN_MAX] THEN ATTR([Value]) END

Now at this point, I did something different, I re-read your question and noted your start point is fixed

So I created a parameter called BASELINE and filled in my MIN_VALUE (you could use the MIN_VALUE instead, up to you)

Finally the difference

DIFF_VALUE = IF LAST() == 0 THEN [BASELINE]-[MAX_VALUE] ELSE NULL END

What you can't see from my explanation above, is the use of addressing and partitioning, that's really important, hence the attached example.

The attached is a bit rough round the edges, but I hope it gives you the idea/ some inspiration

Cheers

Mark

• ###### 13. Re: Calculate difference between oldest and newest date values in workbook

Apologies, I took so long writing my reply, that I hadn't seen this entire thread of answers!

Good work all!

• ###### 14. Re: Calculate difference between oldest and newest date values in workbook

Hi All,

It's me again! One last thought on this subject. Using Derricks excellent LOD calc as an example. Would there be any way to change this calc slightly or take a different approach and have the value calculate dynamically?

I'm happy for the Min Date to stay fixed as it will always be the same on my worksheet. Is there any way to make the MAX Date value dynamic depending on how I change [DATE] in a quick filter on my worksheet?

So simply put Min([DATE]) - whatever the value I change the Date to on my worksheet?

1 2 Previous Next