Documentation Controls and Variables FAQs

Relative Date Variables examples

Chartio’s Relative Date Variables allow you to generate a custom, dynamic date that can be used anywhere you would enter a date value. This convenient variable eliminates the manual process of updating a date value over time as it dynamically updates with the correct date according to the variable selected.

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

Here are some examples:

Using Relative Date Variables as filters

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

Trials created in the current week vs. the previous week

In the Visual SQL interface

  1. Create a chart.

  2. Using the SaaS Company Demo Data data source, add the User Id and Trial Start Date columns from the Subscriptions table to the Columns section. Change the time bucket of Trial Start Date to Week from its aggregation menu. Also add the Trial Start Date column to the Filters section of the Query and use the current week filter operator.

    This Query now calculates the number of trials that started this week

    First query pulls current week's data

  3. Add a second Query and use the same columns from Step 2. But for the Trial Start Date filter, we need to change the filter so we can get the filter from the previous week instead of the current week. To do this, change the filter operator to between and including then use {CURRENT_ISO_WEEK.START.SUB(1,'week')} for the start date and {CURRENT_ISO_WEEK.END.SUB(1,'week')} for the end date.

    {CURRENT_ISO_WEEK.START} and {CURRENT_ISO_WEEK.END} are Relative Date Variables, and we apply the SUB() datetime function to subtract one week from the start of the week and one week from the end of the week. As a result, we get the previous week’s data.

    Second query pulls previous week's data

  4. Click on Merge Queries above the Result Table and change the join type to Union. Click Save to apply the join change.

    Merge the two Queries with Union Join

  5. Now, reorder the rows so the previous week is first followed by the current week. To do this, sort the rows of the Trial Start Date column to Sort rows by ascending.

    Sort rows to bring previous week to the top

  6. Now you can customize your chart as needed. For this example, we found the Bar chart to be the best visualization of this data, but feel free to choose a different chart type! Click Save to Dashboard when you’re finished.

Tip! If you want your chart to display “Current Week” and “Previous Week” like this example, you can add an Edit Formula Action with the following Custom formula to automatically rename the weeks.

case when "Trial Start Date" = strftime('%Y-W%W','now','+7 days') then 'Current Week' else 'Previous Week' end

Note: strftime() ISO weeks start at W00 rather than W01, so we need to add one week to the current date to account for this.

Use Edit Formula to rename the values in the Trial Start Date column

In the Data Explorer interface

  1. Create a chart.

  2. From the Subscriptions table…
    1. Drag the User Id column into the Measures field
    2. Drag the Trial Start Date column into the Dimensions field. Change the time bucket of Trial Start Date to Week of.
    3. Finally, drag the Trial Start Date column into the Filters field and filter for the current week.

    Create a Dataset for the Current Week

  3. This Dataset now calculates the number of trials that started this week. For clarity, let’s rename the Dataset to This Week.

    Click Rename from the Dataset dropdown

  4. Add a second Dataset to query the trials from last week. Since we’ll use the same metrics, we can simply clone our first Dataset by clicking the arrow icon to the right of the Dataset name and selecting Clone.

    Click Clone from the Dataset dropdown

  5. This will be the Dataset for the previous week, so go ahead and rename this Dataset to Previous Week to avoid any confusion.

  6. Change the filter for the Previous Week Dataset to between and including, use Custom Values and enter {CURRENT_ISO_WEEK.START} for the start date and {CURRENT_ISO_WEEK.END} for the end date. {CURRENT_ISO_WEEK.START} and {CURRENT_ISO_WEEK.END} are Relative Date Variables.

  7. The Variables above are the start and end dates of the current week, so you’ll want to slightly modify these to get the previous week’s data. You can subtract 1 week from the start of the week and 1 week from the end of the week to accomplish this, as shown below:

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

    Relative Date variables in Custom Value fields for a filter

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

    Merge the two Datasets with Union Join

  9. 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 the Merge Datasets step 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

  10. Now you can customize your chart as needed. For this example, we found the Bar chart to be the best visualization of this data, but feel free to choose a different chart type! Click Save Chart when you’re finished.

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, what if you wanted to get data for one full week up to yesterday? You can combine these two Relative Date variables, {TODAY.SUB(1,'day')} and {TODAY.SUB(1,'week')}, into the following variable as your start date:

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

We can then use the following as the end date to get the day before today (i.e., yesterday):

{TODAY.SUB(1,'day')}
In the Visual SQL interface
Combine Relative Date functions - Visual SQL
In the Data Explorer interface
Combine Relative Date functions - Data Explorer

This gives us a date range starting from one week and 1 day up until yesterday (Nov 29, 2018).

Example chart data using Relative Date variables

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 datetime units can be found here: Relative Date Variable Functions.

Creating dynamic titles using Relative Date Variables

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.

For example, you could dynamically update today’s date in a chart title by using the {TODAY} variable directly in the title.

New Users: {TODAY}

Dynamic titles

It can also reflect the current value of one of your Dashboard Controls. For example, the chart below includes new users between dates selected in the Calendar Control named CALENDAR_RANGE. As the date values of the Calendar Control could change, you can use the Calendar Control date variables in the chart title so that it always reflects the correct date range.

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

Calendar start and end

Turning Relative Date Variables into timestamps

Relative Date Variables are, as you would guess, simply dates. If you want to use these variables to perform calculations with datetime values, you need to put them in a timestamp format. To do this, you can use a Custom formula to append ' 00:00:00' to the variable to turn it into a datetime data type. Note the necessary space in front of the 00:00:00!

For example, to turn the {TODAY} variable into a timestamp, you’d enter the following in your Custom formula:

{TODAY} || ' 00:00:00'

Turn Relative Date Variables into timestamps