I have a join looks like below. As you can see one of the time filter is hardcoded in the join because I want all data from the left table but only data fall in that time frame from the right table.
My questions is, is there a way I can use parameter to dynamically filter out the time frame in the join clause? Currently it set to view previous week data. Ideally I want to take user input for the date frame.
Thank you!
explore: dim_sku_product {
join: fact_order_item {
relationship: one_to_many
type: left_outer
sql_on: ${dim_sku_product.sku_id} = ${fact_order_item.sku_id} AND
DATETIME_DIFF(DATETIME(TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), DAY), 'UTC'), DATETIME( (TIMESTAMP_TRUNC(TIMESTAMP(${fact_order_item.order_date}), WEEK(MONDAY))), 'UTC'), DAY) >= 7 AND
DATETIME_DIFF(DATETIME(TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), DAY), 'UTC'), DATETIME( (TIMESTAMP_TRUNC(TIMESTAMP(${fact_order_item.order_date}), WEEK(MONDAY))), 'UTC'), DAY) <= 13;;
}
Try creating a derived table based uponfact_order_item that has a date filter exposed to the user and then join that to your dim_sku_product table.