Start and end date calculations

Last activity on October 1, 2023 - 11:21 15k Views 3 Replies Professional ARIS

Charles Collins on August 15, 2011

I want to be able to calculate the difference between a start date and end date in a datafeed.

The columns are both date format dd/MM/yyyy (no times, dates only)

I looked at the arithmetic calculation but that would not take dates only numeric columns

In my datafeed I have the start and end date on a single row and I want to see the difference in terms of number of days.

I also want to calculate the average difference on a number of rows where the difference calculation per row could vary

This obviously needs to accommodate the normal calendar including leap year

Example of the calculation I am seeking to re-create in MashZone using February to demonstrate leap year - is:

28/02/2011	02/03/2011	2 days difference
29/02/2012	01/03/2012	1 day difference
29/02/2012	29/02/2012	0 day difference

The example is in European date format dd/mm/yyyy

I hope someone can help me

Thanking you in anticipation

Charles

Bookmark

Notify Moderator

3 Replies

Charles Collins Author on August 16, 2011 - 13:34

Many thanks for that it worked fine on the first part of my query to be able to calculate the difference between two dates irrespective of the calendar - excellent.

The second part was to be able to calculate the average of a number of rows that have the "days since" in your earlier example.

Using the time filter MashApp to select the rows for calculation based on a filter from the column "Orig_Date" below

The output of this average calculation datafeed is to be assigned to the appropiate MashApp (whatever that is) as a single amount - probably to two decimal points

Ref ID	Orig_Todays_Date	Orig_Date	Today	Dates	Days since
3601	2010-12-30T00:00:00	2010-12-30T00:00:00	1293667200000	1293667200000	0
3600	2010-12-30T00:00:00	2010-12-30T00:00:00	1293667200000	1293667200000	0
104	2011-01-02T00:00:00	2011-01-01T00:00:00	1293926400000	1293840000000	1
803	2011-01-02T00:00:00	2011-01-01T00:00:00	1293926400000	1293840000000	1
3605	2011-01-02T00:00:00	2011-01-01T00:00:00	1293926400000	1293840000000	1
3607	2011-01-02T00:00:00	2011-01-01T00:00:00	1293926400000	1293840000000	1
103	2011-01-02T00:00:00	2011-01-01T00:00:00	1293926400000	1293840000000	1
3608	2011-01-03T00:00:00	2011-01-01T00:00:00	1294012800000	1293840000000	2
804	2011-01-03T00:00:00	2011-01-01T00:00:00	1294012800000	1293840000000	2
3609	2011-01-02T00:00:00	2011-01-01T00:00:00	1293926400000	1293840000000	1
3606	2011-01-02T00:00:00	2011-01-01T00:00:00	1293926400000	1293840000000	1
3604	2011-01-03T00:00:00	2011-01-01T00:00:00	1294012800000	1293840000000	2
3610	2011-01-08T00:00:00	2011-01-02T00:00:00	1294444800000	1293926400000	6
805	2011-01-08T00:00:00	2011-01-02T00:00:00	1294444800000	1293926400000	6
806	2011-01-08T00:00:00	2011-01-02T00:00:00	1294444800000	1293926400000	6
3611	2011-01-03T00:00:00	2011-01-03T00:00:00	1294012800000	1294012800000	0
106	2011-02-01T00:00:00	2011-01-30T00:00:00	1296518400000	1296345600000	2
3615	2011-01-30T00:00:00	2011-01-30T00:00:00	1296345600000	1296345600000	0
3612	2011-02-01T00:00:00	2011-01-30T00:00:00	1296518400000	1296345600000	2
3613	2011-01-30T00:00:00	2011-01-30T00:00:00	1296345600000	1296345600000	0
807	2011-02-01T00:00:00	2011-02-01T00:00:00	1296518400000	1296518400000	0
3617	2011-02-03T00:00:00	2011-02-01T00:00:00	1296691200000	1296518400000	2
3618	2011-02-01T00:00:00	2011-02-01T00:00:00	1296518400000	1296518400000	0
3621	2011-02-01T00:00:00	2011-02-01T00:00:00	1296518400000	1296518400000	0
108	2011-02-06T00:00:00	2011-02-01T00:00:00	1296950400000	1296518400000	5
808	2011-02-01T00:00:00	2011-02-01T00:00:00	1296518400000	1296518400000	0
3619	2011-02-06T00:00:00	2011-02-01T00:00:00	1296950400000	1296518400000	5
3620	2011-02-03T00:00:00	2011-02-01T00:00:00	1296691200000	1296518400000	2

Thanks in anticipation

Charles

or register to reply.

Notify Moderator

Upcoming events

All events

Reset Password

ARIS User Group DACH Meeting zum Thema „One Standard“ & Last-Mile