Templated date filters not working with dynamic measure

JC_U
New Member

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… 

0 1 247
1 REPLY 1
Top Labels in this Space