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.
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?
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
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
You can have that in your SQL parameter as a dimension. And then add the dimension as a filter. @iglooburner
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:
I think I have something that works - I used the relative section after adding work date as a filter.
Thanks,
Derek