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
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
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
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.
