Solved: Dynamic measure with date selected by the user - Page 2

tatuspark · 11-24-2020 02:43 AM

Hello,

I would like to create measures with parameters allowing the user to choose the date (month and year) that he wants to display the sales data of month N / Year N and month N-1 / Year N-1 This is what i do

measure: Nb_de_jours_mois_N {
type: count_distinct

sql: ${TABLE}.VENTE ;;
filters: [dte_vente_date: "last month"]
}

measure: Nb_de_jours_mois_N_1 {
type: count_distinct
sql: ${TABLE}.VENTE ;;
filters: [vente_date:"13 months ago"]
}

But I want it to display according to the date entered in the date filter.

Thanks

Naomi_Johnson

Hi,

You can do this using templated filters.

If you create a date filter and then use that in a case statement within the sql, this allows the end user to dynamically choose the selected timeframe. I’ve created an example of below, using order_items and order_id dimension

filter: date_filter {
  type: date
}

measure: orders_selected_month {
  type: count_distinct
  sql: case when {% condition date_filter %} ${created_raw} {% endcondition %} then ${order_id} end ;;
}

measure: orders_selected_month_ly {
  type: count_distinct
  sql: case when {% condition date_filter %} dateadd(year,1,${created_raw}) {% endcondition %} then ${order_id} end ;;
}

I hope this solves your query. Let me know if you have any questions.

Thanks,

Naomi

View solution in original post