Question

Templated date filters not working with dynamic measure

  • 15 June 2022
  • 1 reply
  • 31 views

I am trying to create a dynamic measure based on two discrete dates that a user selects on the dashboard but running into errors. 

 

First, the data: 

org, date, some_amount, another_amount

company_a, 2022-06-10,2000,5000

company_b, 2022-06-10,3000, 4000

company_c, 2022-06-10, 2500, 3000

company_a, 2022-06-11, 2500,6000

company_b, 2022-06-11, 3500,6000

company_c, 2022-06-11, 2500,6000

 

I created two date filters in my lookml:

filter: date_1 { 

    type: date 

}



filter: date_2 {

    type: date 

}

 

I want the dashboard user to pick two dates on the dashboard to make a dynamic measure; which looks like this currently: 

measure: calculate_on_date_1 {

    type: number

    sql: case when {% condition date_1%} ${date} {% enddcondition %} THEN ${some_amount} / ${another_amount} END

}

measure: calculate_on_date_2 {

    type: number

    sql: case when {% condition date_2%} ${date} {% enddcondition %} THEN ${some_amount} / ${another_amount} END

}

In other words I want to say: ${some_amount} / ${another_amount} WHERE date = date selected by the user 

 

My expected result would then be: 
org, date_1, date_2, percentage_change 

company_a, 40%, 50%, +10%,

company_b, 50%, 51%, +1% 

company_c, 40%, 43%, +3% 

and so on… 

 

 


1 reply

Hello JC, 

If I understand correctly, I believe this should provide the solution you are looking for. We can create hidden yesno dimensions that will filter on our selected dates which will allow us to create filtered measures where we are filtering for only our selected date by setting these yesno dimensions = “Yes”. 

 

Hope this helps! If not let me know and we can keep tackling. I believe this should be exactly or very close to the solution you are looking for!

  filter: selected_date {
type: date_time
}

filter: selected_date_2 {
type: date_time
}

dimension: hidden_selected_date {
hidden: yes
type: yesno
sql: {% condition selected_date %} ${date} {% endcondition %} ;;
}

dimension: hidden_selected_date_2 {
hidden: yes
type: yesno
sql: {% condition selected_date_2 %} ${date} {% endcondition %} ;;
}

measure: calculate_on_date_1 {
type: number
sql: safe_divide(${some_number}, ${another_number}) ;;
filters: [hidden_selected_date: "Yes"]
}

measure: calculate_on_date_2 {
type: number
sql: safe_divide(${some_number}, ${another_number}) ;;
filters: [hidden_selected_date_2: "Yes"]
}

 

Reply