How to create week dimensions that don't start on Monday

Charts

In Chartio, weeks are defined using the ISO WEEK date system which starts on Monday and ends on Sunday. You can read more here, How are weeks defined in Chartio?.

If you want to use weeks that start on any particular day of the week, you can use this method. Note: This current workaround is modifying week start to Sunday instead of Monday. It returns the start of the week starting on Sunday.

PostgreSQL and Redshift

TO_CHAR(date_column + (0 - EXTRACT(DOW FROM date_column)) * INTERVAL '1 days', 'YYYY-MM-DD') + interval '-6 days', 'YYYY-MM-DD'
  • date_column is any date field you wish 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.

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

    Example of SQL workaround


Related Charts Help Articles

See more