8 Replies Latest reply on Feb 17, 2019 10:33 AM by Amogh Katwe

# Date difference between two data points in a line graph

Hello,

I have a line graph that shows value against date-time. There are peak values in the graph and i would like to know the day difference between the peak values.

• ###### 1. Re: Date difference between two data points in a line graph

You uploaded a TWB file, not a TWBX file, so we can't open it.  Do FILE --> Extract packaged workbook

• ###### 3. Re: Date difference between two data points in a line graph

The first step would be to decide what actually constitutes a peak.

I did some calcs for you.  On sheet 1(2) I display two of them -- if the line is dropping, display a "D", and if the line increased, display "I".  (For the record, I took off the dual axis to simplify what we're seeing here.)  On sheet 1(3) I added a "Peak" calc where we see "Peak" when the mark shows an increase from the previous one, and the next mark is a decrease.

On Sheet 1(4) I display only Peak.

So the second "Peak" may or may not constitute a peak for the purpose of your business case.  If not, you'll have to decide how to eliminate such blips.  Sure, you can look at it and visually see that you don't want it.  But you'll have to decide some mathematical or logical formula to exclude it.

Having said that, for now, that's where I stopped.  Once you decide what to include/exclude in this analysis, then I'll work out a way to capture the date from the individual peaks.  (Yes, we can do that.)

• ###### 4. Re: Date difference between two data points in a line graph

This is great. I do not require the second peak. I need to consider only those peaks that have highest 'Did we increase' value.

In my business case. I'd always have these big peaks. I'd like to consider those peaks which have Inventory(sum by difference) value greater than 2000

• ###### 5. Re: Date difference between two data points in a line graph

I don't understand your last statement.  Every value in that chart is greater than 2000.  What is "sum by difference"?

• ###### 6. Re: Date difference between two data points in a line graph

Doing a quick table calculation by 'difference' for inventory, you can see a graph that is of my interest. The only positive values would be greater than 2000 (Hence the peak). This also gets rid of the second peak value we got!

• ###### 7. Re: Date difference between two data points in a line graph

Actually, you can't just do the diff in inventory.  The second good peak has only a 543 diff from the prior mark.

In the attached I added a [Valley] calc.  See Sheet 1(4).  What you're looking for is a diff of at least 2000 between the Peak and the Valley.  That's what we can use to eliminate that blip on 12/28.

Here's how I did that.  Take a look at the calc [Valley Value].  This uses a function called PREVIOUS_VALUE.  It's a recursive function that looks at the value of itself in the previous mark.  So for [Valley Value], each time I hit a new Valley, I set it to the Valley's inventory value.  If I'm not on a valley, I just keep pulling whatever was in the previous mark forward.  You'll see a visual of how that works in a later sheet.

Go to Sheet 1(5).  I made another PREVIOUS_VALUE calc to keep track of number of peaks.  I'll need that on Sheet 6.  When we hit the first peak, the number gets set to 1.  Then it goes to 2 on the next peak, etc.

Go to sheet 6.  I made a final calc called Datediff Peaks.  When I hit a peak, this calcs.  But I skip the first peak because there is nothing to compare it to.

I made a [Previous Peak Date] calc to pull the previous peak's date forward.  then in [Datediff Peaks] I do a datediff of the prior mark's [Previous date] value, and the current mark's date.  there were 20 days between peak 1 and peak 2, and 15 days between Peak 2 and Peak 3.

So the key is to identify what marks you want to do the math on.  That's done with the [Peak] calc.  When it's set, do the work.  And you drag the values you need from mark to mark using the previous_value function.

2 of 2 people found this helpful
• ###### 8. Re: Date difference between two data points in a line graph

This is perfect and this is a big work for me. Thank you for the explaining your work.