
1. Re: Calculate difference between oldest and newest date values in workbook
Derrick Austin Mar 23, 2016 7:19 AM (in response to Tony Ball)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
Tony Ball Mar 23, 2016 7:36 AM (in response to Derrick Austin)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
Nicholas Hara Mar 23, 2016 7:38 AM (in response to Tony Ball)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
Derrick Austin Mar 23, 2016 7:43 AM (in response to Tony Ball)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
Tony Ball Mar 23, 2016 7:47 AM (in response to Nicholas Hara)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.

amps sample workbook.twbx 38.8 KB


6. Re: Calculate difference between oldest and newest date values in workbook
Tony Ball Mar 23, 2016 7:49 AM (in response to Derrick Austin)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
Nicholas Hara Mar 23, 2016 7:52 AM (in response to Tony Ball)1 of 1 people found this helpfulHi Tony,
Have you tried using a Percent Difference Quick table calculation?
1. Rightclick on Sum(Reading)
2. Select Quick Table Calculation> Percent Difference
3. Rightclick on Sum(Reading)
4. Select Relative To>First
5. Format your numbers and labels: Formatting Numbers and Dates  Tableau Software

9. Re: Calculate difference between oldest and newest date values in workbook
Tony Ball Mar 23, 2016 8:00 AM (in response to Derrick Austin)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
Derrick Austin Mar 23, 2016 8:05 AM (in response to Tony Ball)1 of 1 people found this helpfulYou 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)}

11. Re: Calculate difference between oldest and newest date values in workbook
Tony Ball Mar 23, 2016 8:10 AM (in response to Derrick Austin)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
Mark Fraser Mar 23, 2016 8:18 AM (in response to Tony Ball)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 reread 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

203420.twbx 39.2 KB


13. Re: Calculate difference between oldest and newest date values in workbook
Mark Fraser Mar 23, 2016 8:21 AM (in response to Mark Fraser)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
Tony Ball Mar 24, 2016 4:07 AM (in response to Tony Ball)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?