I am working with an Intelligence Sheet line chart backed by live data that updates daily. I want a deviation line that always compares today (the latest populated day) against the same day last year, and updates automatically as new data lands each day.
When I add a deviation line referencing "today," it locks to the specific day it was plotted on rather than following the newest day in the data. So the next day, the line still points at the original date instead of moving forward with the latest data.
Thanks!
Freja - The reason "today" locks in place are that a deviation line built by referencing a specific date (or via Create your own / Custom range) resolves that reference once, at the moment you configure it, and stores it as a fixed value. It's not a live formula — it's a snapshot of what "today" happened to be on the day you set it up. So tomorrow, the line is still pointing at yesterday's "today."
First to Last (and First to Last Actual) work differently: instead of storing fixed dates, they're computed fresh from whatever data is currently in the visual, every time it renders. "First" = the leftmost point in the chart's data, "Last" = the rightmost. As new rows land each day, the rightmost point shifts forward automatically — no re-plotting needed.
We also understand the point that it only works for selected few cases in the dropdown, we will work on providing a solution by which you can select relative comparison with reference from First / Last as well.
Thanks for the update pvjraghavan. This clears up why it froze.
When it comes in, will the relative offset be something I set like Last minus n year/month/week/day?