Looker Community

Subtracting days from date_start

Hey guys,

When I user select a date range, I want to make sure that, the date_start is subtracted by 30days. This will be used in order to build a proper attribution model for this look.

Conceptually, what I tried to do is:

view: item_health_target_hitting {
  derived_table: {
    sql: 
SELECT
          ymd, item_id,
          sum(cost) AS cost,
          sum(clicks) AS clicks
     FROM
         performance_table
     WHERE
          ymd >= {% add_days(date_start,-30) date_filter %}
          AND ymd <= {% date_end date_filter %}
          AND cost > 0
     GROUP BY
          ymd, item_id ;;

filter: date_filter {
    datatype: yyyymmdd
    type: date
  }

The condition on the date_end is working as intended, but the date_start really needs to be subtracted by 30 days. I am currently using Impala, and I tried many things, with no success.

Instead of {% add_days(date_start,-30) date_filter %},
try add_days({% date_start date_filter %},-30).
(I’m assuming that’s the day-adding function in Impala.)

@menashe
Thanks for the tip!
I actually had to do a lot of casting to make sure that impala could subtract it:

WHERE
          ymd >= cast(from_timestamp(adddate(cast(unix_timestamp(cast({% date_start date_filter %} AS string), 'yyyyMMdd') AS timestamp), -30), 'yyyyMMdd') as bigint)
          AND ymd <= {% date_end date_filter %}
          AND cost > 0

Not neat, but it helped me go through less partitions in my cluster :slight_smile:

Hey Heber!

I believe the reason might be that you’re trying to execute SQL within the Liquid, where Liquid is just a shortcut syntax. What happens if you try to modify it to:

ymd >= add_days({% date_start date_filter %},-30)

Edit: oops! Glad menashe got in here first :slight_smile:

Hey @bens,
I had actually tried that, but it didn’t work. The adddate function on Impala required the date field to be a timestamp, which in this case it is not. (See below)

I mean, by using this crazy amount of casting, I was able to achieve it, but I wish there was a lookml function to subtract some days out of a date field easier.