Sign in
x
☰

Date/time values

Updated at 2019-06-26 01:56:29 UTC
Cumulative distribution chart showing survey completion dates over time by country. Five colored lines (Overall, Germany, Spain, France, Italy) track the percentage of completed surveys from March 25 to April 15, 2018, with all reaching 100% by the end of the period.

Your survey data might have one or more columns with date values. There are various ways you can parse and analyze dates in Protobi.

For instance, most surveys have an interview completion date:

Spreadsheet view showing the lastcomplete column containing date-time values in ISO 8601 format. Sample values include "2018-04-08T16:09:28.903+00:00" and several other timestamps from April 2018.

Unless your data file identifies the column as date values, Protobi interprets date strings like any other string, and shows a distribution of the string values, treating each string as a categorical value:

Protobi frequency table showing lastcomplete variable treated as categorical string values. The table displays a long list of unique ISO 8601 date-time strings (like "2018-03-29T21:12:33.16+00:00") each with a frequency of 1, demonstrating the problem of treating dates as categorical text.

Parse dates

You can identify an element as date values by selecting "More properties..." from the edit icon and setting type to "date." You can also do this in the "Edit JSON..." dialog by setting "type": "date".

Advanced properties dialog for the lastcomplete field with a dropdown menu showing type options. The "date" option is highlighted in the list which includes string, number, date, empty, and other data types.

Protobi now parses the date strings as date values. You can see that the data now shows the full ISO Date-Time value: 

Protobi frequency table showing lastcomplete variable after being parsed as date type. Date values now display in ISO format with timezone (like "2018-03-21T16:34:02.017Z") and each shows a frequency percentage of 0.3% with small bar indicators.

ISO date format

Here, Protobi was able to easily interpret the strings because they came in a standard date time format that is recognized by all browsers. This particular format, YYYY-MM-DD is the ISO 8601 format. It might look unusual but this is the international standard date formats, as noted in this XKCD Cartoon (other formats exist like RFC 2822).

Custom date formats

It's possible your dates come in a different, reasonable but non-standard format. Below we see dates in the format "MM/YYYY":

Protobi view showing a transformed date variable q14x2 displaying dates in MM/YYYY format (like "01/2018", "04/2018", "02/2018"). The frequency table shows various months from 2016-2018 with percentages ranging from 1.8% to 4.8%, plus [NA] at 37.2%.

Protobi already includes the leading date-time library moment.js that can parse a very wide variety of dates. To specify a specific format, select "Edit JSON..." from the edit icon and in the editor add the attribute "dateFormat": "MM/YYYY"

Cumulative charts

Protobi charts recognize date values. For instance set the chart type to cumulative:

Chart configuration dialog showing chart type and options. The "cumul" (cumulative) chart type is selected in the Chart type section, and "Cumulative distribution" is highlighted in the Chart options section. Legend position is set to "right".

The chart now shows a cumulative distribution with the date values on the horizontal axis:

Cumulative distribution line chart for lastcomplete variable showing survey completion over time. A single blue line progresses from 0% on March 25, 2018 to 100% by April 15, 2018, with the steepest increase occurring around April 1.

Transform dates into different values

You can transform dates to a specific value. For example, the lastcomplete variable records date and time. But we can cluster the values into discrete days, and ignore the time values using the transform dialog.

Transform group dialog showing date transformation options. The "Day" option is selected (filled radio button) among various choices including None, Sum, Mean, statistical functions, and date-specific transforms like Year, Quarter, Month, Day, Hour, Day name, and Day of week. Cumulative distribution line chart for lastcomplete with dates transformed to discrete days. The blue line shows survey completion from March 25 to April 15, 2018, with visible circular markers at each daily data point, making the discrete day grouping evident.

Here, we also added markers by using the Plotly option, "chartOptions.mode": "lines+markers" to make it easy to see that values are grouped into discrete days.

Other transform options include values by the day of week or month. When transformed, the values are still dates, but rounded to the nearest value. They still retain their broader context like year, so "Feb 17, 2017" and "Feb 17, 2018" are recognized as different days. 

Values include the integer index so that they sort correctly:

Protobi frequency table showing lastcomplete transformed to day of week. Seven rows display weekdays as "1-Mon" through "7-Sun" with frequencies ranging from 4.5% to 34.2%, plus a sample size N of 1203. Thursday (34.2%) has the highest completion rate.

Date and time charts can also be crosstabbed like other elements. For instance, completion date by country:

Crosstab table showing lastcomplete dates by Country. Rows display dates from 4/1/2018 to 4/18/2018 (8:00:00 PM), and columns show countries: Australia, Germany, Erie, Greenland, and Mexico. Cell values show the count of survey completions for each date-country combination.

Date and time values often arise in context of more advanced analytics goals, such as intervals, and regimens. See our Date/time calculations tutorial to learn more.