有才！隋柯名一人饰两角 翻唱《三生三世》片尾曲走红

Question

I am trying to use Templated Filters logic in LookML to filter a look/dashboard based on flexible dates i.e., whatever date value the user enters in the date filter (transaction_date_filter dimension in this case). This is my LookML:

    view: orders {
    
    derived_table: {
    
    sql:
    select
    customer_id,
    price,
    haspaid,
    debit,
    credit,
    transactiondate,
    case when haspaid= true or cast(transactiondate as timestamp) >= date_trunc(cast({% condition transaction_date_filter %} cast(transactiondate as timestamp) {% endcondition %} as timestamp),year) then debit- credit else 0 end as ytdamount
    FROM
    orders ;;
    }
    
    dimension: transaction_date_filter {
    type: date
    sql: cast(${TABLE}.transactiondate as timestamp) ;;
    }
}

I get this error:

Invalid cast from BOOL to TIMESTAMP

Below is the rendered BQ SQL code from the SQL tab in the Explore when I use the transaction_date_filter as the filter,

select
    customer_id,
    price,
    haspaid,
    debit,
    credit,
    transactiondate,
    case 
        when haspaid = true or cast(transactiondate as timestamp) >= date_trunc(cast((cast(orders.transactiondate as timestamp) < (timestamp('2025-08-04 00:00:00'))) as timestamp), year)  
            then debit - credit 
            else 0 
    end as ytdamount
from
    orders

Can someone please help?

Answer 1

As I understand, your code is inserting a filter that checks yes or no, but it was treated like a timestamp instead, it’s like “turn yes to date” that’s why it crashes and gives an error of “invalid cast from BOOL to TIMESTAMP”

Try this if this works:

Remove the cast part

view: orders {
   derived_table: {
     sql:
        SELECT
          customer_id,
          price,
          haspaid,
          debit,
          credit,
          transactiondate,
          CASE 
            WHEN haspaid = TRUE OR {% condition transaction_date_filter %} transactiondate {% endcondition %}
           THEN debit - credit
           ELSE 0 
          END AS ytdamount
        FROM orders ;;
}

dimension: transaction_date_filter { 
   type: date
   sql: ${TABLE}.transactiondate ;;
  }
}