CC

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

by Stephan Freudl
Posted on Tue, 08/16/2011 - 08:54

Hi,

to do some math based on dates you need to convert their column type to number first. It has been asked and described recently.

Once a date has been converted into number it represents the number of milliseconds since 1970. So all the leap year stuff remains transparent.

Regards

Stephan

by Charles Collins Author
Posted on Tue, 08/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

by Stephan Freudl
Posted on Wed, 08/17/2011 - 12:54

Depends on what your chart should consist of. In a, for instance, bar chart the average duration on y-axis, i.e. height of bar, seems to be fine. But what's on the x-axis? One bar per distinct value of "ref id"? MashZone will automatically aggregate rows having the same "ref id" value.

This video might provide additional help.

#### Featured achievement

Rookie
Say hello to the ARIS Community! Personalize your community experience by following forums or tags, liking a post or uploading a profile picture.
Recent Unlocks
• SS
• MZ