Average time between dates


I have a table much like this:

InvoiceNo InvoiceDate CustomerID
554132 2018-05-23 12360
553749 2018-08-12 12360
571705 2018-10-13 12360
554321 2018-03-23 11234
553849 2018-08-13 11234

I would from this like to calculate the average amount days between each order for each customerID and add this to a new column on my aggregated customer table. How would I go about doing this and with which nodes/formulas?

Thanks for the help!

I’d go for the following setup:

1 Like

Hi @qqilihq, thanks for taking the time to help.

I’ve got as far as this:

Screen Shot 2018-10-31 at 20.20.22

But the Lag Column node only replicates the first invoice date and does not give me an time between the entries in terms of time between each order for each customer. Any ideas?

Difficult to tell just from looking at the screenshot, but have you verified that the Lag Column node has been configured correctly? I’ll attach a sample workflow which shows the idea:


I’ve just noticed that the same thing can also be achieved without the “Lag Column” node if you configure the “Date&Time Difference” node to use the previous row as an input (see also sample workflow).

Hope that helps.

Lag_Column_Example.knwf (15.0 KB)

1 Like

Hi @qqilihq and thanks for sending this over. I tried to run the workflow with data but for some reason the average time in Node 4 amounts to 0. See attached workflow:Lag_Column_Example_with_data.knwf (87.1 KB)

Also, is there a way to get the average time between orders for each customer using the node set up in the file?

Thanks again!

Hi there,

I selected ‘days’ as granularity, and these are all rounded down to zero in your case. You can select different granularities in the Date&Time Difference configuration.

To detect this information on a customer basis, you make make use of the Group Loop Start node:

Thanks fo the reply and help. I will try different granularities to see if this helps.

Hi @qqilihq may I know how to apply these concept on a customer basis with group loop start? A little bit background here, I’m still not a pro in knime and I would like to know how to apply this

Hello @rizki3105,

welcome to KNIME Community!

Group Loop Start node allows you to calculate/process in each iteration different group or in this case different customer. Check this workflow example to see how it works:

In case you have a different use case than one discussed in this topic feel free to open up a new topic, add some details and I’m sure you’ll get solution/advice/suggestion :wink:


1 Like