dynamically select dashboard time period with parameter

[shakermakerk]

Hello,  I want my users to use a "filter" (parameter) to select the time period of the dashboard, e.g. date, week, month.  I got it working but only by setting the value format has to be string, and this doesn't look tidy. Is there a way to use liquid to define the data type i.e. is there a way to use liquid for the "type" field?

This is an example of my parameter field and the problem:

The parameter:

parameter: time_period_selector{
type: unquoted
allowed_value: {
label: "Daily"
value: "date"
}
allowed_value: {
label: "Weekly"
value: "week"
}
allowed_value: {
label: "Monthly"
value: "month"
}
default_value: "week"
}

Dimension:

  dimension: dynamic_date {
    type: string
    datatype: date
    sql: date_trunc(${TABLE}.transaction_date, {% parameter time_period_selector %}) ;;
  }

 The output for monthly is as a string date, is there a way to ensure that the format matches the parameter? 

[Saudamini · Accepted solution]

Hi,

You can try the below to ensure the format matches the parameter:

Ensure transaction_date is set-up as a dimension_group:

dimension_group: created {
    type: time
    timeframes: [time, hour, date, week, month, year, hour_of_day, day_of_week, month_num, raw, week_of_year, month_name]
    sql: ${TABLE}.transaction_date ;;
  }

Create the time_period_selector parameter:

parameter: time_period_selector{
type: unquoted
allowed_value: {
label: "Daily"
value: "date"
}
allowed_value: {
label: "Weekly"
value: "week"
}
allowed_value: {
label: "Monthly"
value: "month"
}
default_value: "week"
}

Create the dynamic_date dimensions as below to reference the dimension_group created as appropriate:

  dimension: dynamic_date {
    sql:
    {% if time_period_selector._parameter_value == 'daily' %}
      ${created_date}
    {% elsif time_period_selector._parameter_value == 'weekly' %}
      ${created_week}
    {% elsif time_period_selector._parameter_value == 'monthly' %}
      ${created_month}
    {% else %}
      NULL
    {% endif %};;
  }

You can also user Liquid to format dates, more details on this page.