Documentation Data Pipeline FAQs

Define weeks in Data Explorer

Weeks in Chartio are defined using the ISO week date system where weeks start on Monday and end of Sunday. A week is specified by the ISO year in the format of YYYY followed by the week number in the format ww prefixed by the letter W.

Chartio uses ISO weeks because it is an industry standard issued by the International Organization for Standardization (ISO) to avoid misinterpretation of numeric representations of dates. Additionally, every data source type available in Chartio supports ISO weeks.

Definition of weeks in Chartio

Create week dimensions that don’t start on Monday

If you’d like to use a different date range for your weeks, you can specify the start and end date in a few ways:

SQL Mode

If you wish to change the start of the week to another day, you can do this by switching your chart to SQL Mode and manipulating the date column. This is only recommended for table charts, as x-axis date labels expect ISO weeks.

If you want to use weeks that start on any particular day of the week, you can use the method outlined below.

Note: This method modifies the week start to Sunday instead of Monday. It returns the start of the week starting on Sunday.

Below is the formula which can be used to modify the start of the week for PostgreSQL or Redshift Data Sources:

TO_CHAR(date_column + (0 - EXTRACT(DOW FROM date_column)) * INTERVAL '1 days', 'YYYY-MM-DD') + interval '-6 days', 'YYYY-MM-DD'

The date_column can be replaced with any date field for which you’d like to modify the week start and the day of the week (0-6; Sunday = 0).

The query always shifts the date to the next chosen week start (in this case, the next Sunday) and then offsets to the correct week start. Depending on what database you’re using, you will have to adjust the queries above to utilize the functions available for your database.

Below is an example of using the workaround to count how many users are created grouped by week started on Wednesday (Wednesday = 2).

Example of changing the week start in SQL Mode

Using Relative Dates

You can use our Relative Dates in order to manipulate automatically generated date values to fit your desired date range in your chart filters, Dashboard Controls, and the Data Pipeline.

For example, in order to edit a Dropdown so it reports activity from Sunday to Saturday, you can use a date range in your filter using Relative Date Variables such as:

{CURRENT_ISO_WEEK.START.SUB(1, 'day')}
{CURRENT_ISO_WEEK.END.SUB(1, 'day')}

This will subtract one day from the start and end of the current ISO week, selecting the days Sunday through Saturday. However, this approach may cause issues with data spanning multiple years.