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

Charles Collins Author on August 16, 2011

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

Reset Password