Question

Dynamic dimension based on date filter

  • 16 September 2020
  • 2 replies
  • 284 views

Hey guys,

I am currently trying to create a dynamic dimension based on the value of a filter. Simple you would say. But what if the filter is date type and I would like to make the dimension dependent on the timeframe (time granularity) the user picks from the date filter


Here is what a small example that does NOT work:


  filter: my_date_filter {
description: "This is my date filter"
type: date_time
}

dimension: display_time_range {
label: "Display time range"
can_filter: no
sql:
{% if my_date_filter.timeframe == 'week' %}
${TABLE}.week
{% elsif my_date_filter.timeframe == 'month' %}
${TABLE}.month
{% else %}
${TABLE}.day
{% endif %};;
}

Any ideas?

Thank you


2 replies

I too am looking on how to solve this. How can we take the start date from a date range filter in a dashboard and reference that in our dynamic dimension (or would it need to be a dynamic measure if it’s getting a count of users for example?)

Userlevel 4

 

  parameter: date_granularity {

    label: "Date Granularity Filter (D/W/M/Q/Y)"
    description: "For dynamic Delivery period Granularity. Use with dynamic Dimension Date Granularity"
    type: string
    allowed_value: {value:"Day"}
    allowed_value: {value:"Week"}
    allowed_value: {value:"Month"}
    allowed_value: {value:"Quarter"}
    allowed_value: {value:"Year"}
  }

  dimension: date {
    group_label: "Deliverydate Granularity"
    label: "Deliverydate Granularity"
    description: "For dynamic Delivery period Granularity. Use with Filter Date Granularity"
    sql:  CASE
      WHEN {% parameter date_granularity %} = 'Day'
        THEN ${dim_blockhour.bki_delivery_date}
      When {% parameter date_granularity %} ='Week'
        THEN ${dim_blockhour.bki_delivery_week}
      WHEN {% parameter date_granularity %} = 'Month'
        THEN ${dim_blockhour.bki_delivery_month}
      WHEN {% parameter date_granularity %} = 'Quarter'
        THEN ${dim_blockhour.bki_year_quarter}
      WHEN {% parameter date_granularity %} = 'Year'
        THEN ${dim_blockhour.bki_delivery_year}::Varchar
      ELSE NULL
    END ;;
    hidden:  no
  }


 

 

this works for me

Reply