Dynamic dimension based on date filter

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

1 2 630
2 REPLIES 2

sten
Participant I

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?)

moebe
Participant V
  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

Top Labels in this Space
Top Solution Authors