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 update the date value with time, it will always automatically be updated with the correct date according the 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 trials 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 use the following Relative Date variables:

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

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, then 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

You can now customize your chart as needed and click on 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

At times, they may not be as flexible as you’re looking to get. In this case, you can combine Relative Date functions using the {TODAY} Relative Date.

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

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

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

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

Relative Date Filters

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

You can combine different units with the {TODAY} relative date 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.


Related Variables Help Articles

See more