"Invalid cast from BOOL to TIMESTAMP" error in LookML/BigQuery

Looker Q&A Looker
1

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). Below 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 the below error,

Invalid cast from BOOL to TIMESTAMP

Can someone please help?

2

First of all, I do not think you should be using a Derived Table in this use case. Simply model this on top of the orders table directly.

Second, your ytdamount sql should be broken out into more dimensions. Use a yesno for the case statement’s when portion. Then a dimension for debit-credit (perhaps “net debit” or similar). Finally add a measure which does an aggregation on the dimension with a filter on the yesno.

After that, i imagine your invalid cast from bool to timestamp will sort itself out. What I mean to say is, the way you’ve built this is too monolithic for good quality LookML, and as you break it out into more reusable/atomic components I imagine you will find your troubleshooting much easier.