Working with Relative Date variables

Variables

Chartio’s Relative Date variables allow you to generate a custom date that can be used anywhere you would enter a date value. This prevents needing to manually update the date value over time; it will always automatically be updated with the correct date according to the variable selected.

Using Relative Date variables as filters

Relative Date variables can be used as parts of other variables, in your queries, in the Pipeline, and even in chart titles.

In this example, we will create a chart to compare this week’s trials to the previous week’s using our SaaS Company Demo Data Data Source.

Trials

1. Create a chart, then add the User Id column from the Subscriptions table into the Measures and the Trial Start Date column into the Dimensions. Change the time bucket on Dimensions to Week of. Add the Trial Start Date column in the Filters and filter for the current week.

This Dataset now calculates the number of trials that started this week.

Set a filter for the Current Week on the first dataset

2. Add a second Dataset to query the trials from last week. Since we’ll be using the same metrics, we can simply clone our first Dataset by clicking the arrow on the Dataset 1 above the Data Pipeline and selecting Clone.

Clone Dataset

3. Edit the filter for the Dataset clone to Custom Values and to use the following Relative Date variables in the start and end fields:

{CURRENT_ISO_WEEK.START}
{CURRENT_ISO_WEEK.END}

You’ll want to slightly modify these to subtract 1 week from the start of the week and 1 week from the end of the week to get the previous week’s data similar to the Relative Dates below:

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

date filter in chartio

4. Click on Merge Datasets in the Data Pipeline and change the Merge type to Union. Click Apply & Close when done.

Union Join

5. To reorder columns to get the previous week first followed by the current week, you’ll need to add a Pipeline Step. To do this, click the plus sign underneath Merge Datasets in the Pipeline and choose Sort Rows. Select the Week of Trial Start Date column and select the Ascending sort. Click Apply & Close to apply the changes.

Sort Rows to reorder columns

You can now customize your chart as needed and click Save Chart when completed.

Tip: If you want your chart to display “Current Week” and “Previous Week” like this example, you can add an Edit Column Pipeline step with a custom formula like below to automatically rename the weeks.

CASE WHEN "Week of Trial Start Date" = '2019-W'|| strftime('%W','now') THEN 'Current Week'
ELSE 'Previous Week' END

Combining Relative Date functions

Relative Date variables are useful when looking to filter your data on flexible date ranges. At times, they may not be as flexible as you’re looking to get. In this case, you can combine Relative Date functions within your Relative Dates.

For example, here we’d like to get data for one full week up to yesterday. We can then combine Relative Date variables: {TODAY.SUB(1,’day’)} and {TODAY.SUB(1,’week’)} into the following variable as our start date:

{TODAY.SUB(1,'day').SUB(1,'week')}

We can then add the following as the end date to get the day before today:

{TODAY.SUB(1,'day')}

Relative Date Filters

As shown below, this would give us a date range from one week and 1 day up until yesterday (Nov 29, 2018).

Example chart data

You can combine different units with {TODAY} or with other Relative Dates as needed. The option to combine Relative Date functions is also possible using our other Relative Date variables.

Additional information regarding Relative Dates and available units can be found here: Relative Date Variable Functions.

Creating dynamic titles using Relative Dates

You may want your chart titles to reflect certain dynamic or changing values such as today’s date or a variable’s current value. You can do this by integrating these variables directly into your chart’s title.

Your title can reflect Relative Dates such as today’s date by just inputting the Relative Date into as part of the title:

Dynamic titles

It can also reflect the current value of one of your dashboard variables. For example, the chart below includes new users between dates selected in the CALENDAR_RANGE Calendar variable. As this value is continually changing, we have included the Calendar variables into the chart title so that it always reflects the correct date range.

New Users - {CALENDAR_RANGE.START} - {CALENDAR_RANGE.END}

Calendar start and end


Related Variables Help Articles

See more