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…