Creating a 2 Day Lag in Looker Chart

I am trying to create a 2 day lagged report because my data is not complete until 2 days after the work date. 

For example, my chart today shows data through 3/22/2021.  However, 3/21 and 3/22 are not complete - which skews the charts.  I want to only have the chart showing through 3/20. 

Is there a way to automate so the chart always shows data that is two days old.  The default pulls in the most recent day which as I mentioned is not complete and skews the data.

0 8 1,033
8 REPLIES 8

Dawid
Participant V

I think the best way to achieve this would be with a filter. You could create a dimension that holds a number (offset) from today’s date. -1 for yesterday (or 1 if you prefer), -2 for day before. Then your filter would be offset < -2, or offset > 2 if you choose to deal with positive numbers. 

Hi Dawid - what does the code look like in the dimension?  I tried using the now() function but get an error.

Also, when I add a custom dimension, I don’t have the option to select as a filter.  Should that be possible?

Dawid
Participant V

In MySQL for example this would give you day offset:

DATEDIFF('2021-03-24', CURRENT_DATE)

Then you can use it in a dimension sql parameter and filter by it. 

It would be a different story if you still want to use date/timeframe filters in Looker, like if you select Last 2 weeks, to actually include only days -16 to -2, rather than -14 to 0

Kehinde
Participant II

I think the better way of going about this is actually using date add instead of date diff

DATEADD("2021-03-22", -2, day)

This should work @iglooburner @Dawid 

Kehinde
Participant II

You can have that in your SQL parameter as a dimension. And then add the dimension as a filter. @iglooburner 

Kehinde
Participant II

The filter can be date <= date_dimension. This will always take out the last two days.

Mark as done if this helps. @iglooburner 

When I use that code to try to create a custom dimension I get the following error:

097c6122-7b06-44ed-96fb-c795bc469e1c.png

658204e6-2fb3-40d2-8e1d-21baddf4b459.png

I think I have something that works - I used the relative section after adding work date as a filter.

Thanks,
Derek