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

0
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

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

0

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
  • Profile picture for user kbiront
  • Profile picture for user Tony Iliev
  • Profile picture for user amandeep.7.singh
  • PacMan

Leaderboard

|
icon-arrow-down icon-arrow-cerulean-left icon-arrow-cerulean-right icon-arrow-down icon-arrow-left icon-arrow-right icon-arrow icon-back icon-close icon-comments icon-correct-answer icon-tick icon-download icon-facebook icon-flag icon-google-plus icon-hamburger icon-in icon-info icon-instagram icon-login-true icon-login icon-mail-notification icon-mail icon-mortarboard icon-newsletter icon-notification icon-pinterest icon-plus icon-rss icon-search icon-share icon-shield icon-snapchat icon-star icon-tutorials icon-twitter icon-universities icon-videos icon-views icon-whatsapp icon-xing icon-youtube icon-jobs icon-heart icon-heart2 aris-express bpm-glossary help-intro help-design Process_Mining_Icon help-publishing help-administration help-dashboarding help-archive help-risk icon-knowledge icon-question icon-events icon-message icon-more icon-pencil forum-icon icon-lock