What shift: parameter does is some sort of “cheating” 😉. It shifts the timeframe specified in the basic query parameters and maps the resulting data points to timestamps from the original timeframe. Because I used lookup requires joining condition I used timeframe frame, because it was the same due to this effect. Logically in this case join condition is not needed, because both queries will return always 1 record.
You can observe this effect on chart when we remove aggregation to single value. I used append so we can have 2 different records, each with individual timeframe. This query:
timeseries tc=avg(dt.runtime.jvm.threads.count), from: "2024-07-01T00:00", to: "2024-07-02T00:00"
| append [
timeseries tc_ref=avg(dt.runtime.jvm.threads.count), shift:-1d
]
Produces this result:
black line represents data for June 30th, but it is mapped into July 1st
Result of such query:
timeseries tc=avg(dt.runtime.jvm.threads.count), from: "2024-07-01T00:00", to: "2024-07-02T00:00"
| append [
timeseries tc_ref=avg(dt.runtime.jvm.threads.count), from: "2024-06-30T00:00", to: "2024-07-01T00:00"
]
looks like this:
and in raw form:
As you can see timeframe field is different, so when used in lookup join condition it did not connect records.
There are many possible solutions. This way:
timeseries tc=avg(dt.runtime.jvm.threads.count), from: "2024-07-01T00:00", to: "2024-07-02T00:00"
| append [
timeseries tc=avg(dt.runtime.jvm.threads.count), from: "2024-06-30T00:00", to: "2024-07-01T00:00"
]
| fields timeframe, tc = arrayAvg(tc)
result will be presented with 2 rows and by looking at timeframe column, you can see what is actual and what is reference value:
but you can always combine this into single row:
timeseries tc=avg(dt.runtime.jvm.threads.count), from: "2024-07-01T00:00", to: "2024-07-02T00:00"
| append [
timeseries tc_ref=avg(dt.runtime.jvm.threads.count), from: "2024-06-30T00:00", to: "2024-07-01T00:00"
]
| summarize {tc=takeAny(arrayAvg(tc)), tc_ref=takeAny(arrayAvg(tc_ref))}, by: {metric="dt.runtime.jvm.threads.count"}