Selecting last date of each time aggregation

[hbrandao]

Hello everyone,

Taking into consideration the view detailed below, I am trying to check the distinct count of ids per day. So far, so good.

The problem happens when I change my timeframe_picker from day to week/month. When those are selected, I would like to select the last day of this time aggregation and perform a count, instead of counting between the timeframe.

Ex. If I select “Month” and I am in the 2020-01, I would like to perform a count of distinct ids on the 2020-01-31 only.

Not sure how to tweak my code to make this happen, but I ran out of ideas.

dimension_group: partition {
    type: time
    sql: ${TABLE}.ymd ;;
    datatype: yyyymmdd
    timeframes: [date, week, month, year]
    hidden: yes
  }

  filter: date_filter {
    type: date
    datatype: yyyymmdd
  }

  parameter: timeframe_picker {
    label: "Time Interval"
    type: unquoted
    allowed_value: {value: "Day"}
    allowed_value: {value: "Week"}
    allowed_value: {value: "Month"}
    default_value: "Week"
  }

  dimension: dynamic_timeframe {
    label_from_parameter: timeframe_picker
    sql:
    {% if timeframe_picker._parameter_value == 'Day' %}
      ${partition_date}
    {% elsif timeframe_picker._parameter_value == 'Month' %}
      ${partition_month}
    {% elsif timeframe_picker._parameter_value == 'Week' %}
      ${partition_week}
    {% endif %};;
    type: string
  }

  dimension: id {
    type: number
    sql: ${TABLE}.id ;;
    primary_key: yes
  }

  measure: distinct_id {
    type: count_distinct
    sql: ${id} ;;
    drill_fields: [detail*]
  }