How to manipulate time filter using parameters for time comparisons

How to use single filter widget in the dashboard to control different time ranges.

For example, we want the date range filter to be set Feb 1- 14, but some visuals to show 2 weeks before (Jan 15-30) and some visuals showing 2 week after (Feb 15-30).

You can do this using custom parameter expressions in the filters of the visuals.

First, remember that all filter widget set parameters, which you can view in the funnel at the top right:

You use any of these parameters in your visualizations using the special syntax:

    <<parameter:default>>

In this example use-case, we can write a filter expression as such to manipulate the date range we expect:

[order_date_string] between date_sub(cast('<<order_date_string.start>>' as timestamp), interval 2 weeks) and date_add(cast('<<order_date_string.end>>' as timestamp), interval 2 weeks)

You want to enter this expression in the filter shelf element as show below:

Refer to this page for more info around date_add / date_sub functions (https://www.cloudera.com/documentation/enterprise/5-13-x/topics/impala_datetime_functions.html)

NOTE - disable dataset scope

When using custom filter expressions, you want to disable dataset scope filtering especially if the filter widget comes from the same dataset as the visual.

You can do this by enabling “explicit scope” from Settings -> Parameters

2 Likes

Hi Shaun ,
Does this still apply for new version?
Because I tried with same expression and i get Validation Error: near “2”: syntax error.
Can you please help with this.
My requirement was on a click of one date ,needed to display trend of 1month earlier data in line chart.

@Ashwini_Shetty yes, this is still valid on newer versions.

  1. What version are you using?

  2. What does your expression look like?

  3. What connection are you using? some of the functions like “date_sub” will not work across all connection/DB types

1 Like

Ok.I use version 4.5 but yes like you said date_sub isnt working because connection is SQLite (CSV file).

Thanks .Ill try with other connection

HI Shaun,

i have two columns, in one i’m using the syntax like <<dim.data:’ ‘>> and in the other column which is specific to date range i’m using the syntax like cast(’<<dat.end>>’ as timestamp). Visually i’m getting the right result.

when i’m trying to create an AV then i’m getting the following error saying

Unable to substitute parameter: <>

Can you please guide me

@Purnesh you want to always specify a default value when using the parameter syntax.

<<parameter:default>>

So in your case you wan to include something like:

'<<date.end:2000-01-01>>'

Or sometimes you can have it be blank depending on your use-case:

'<<date.end:>>'