KQL Query Date filter on non timeseries data

I'm tying to use Lens to plot data changing over a period of time. We generate the data once a week at single point in time, and so it doesn't look good as a time series. We also want to plot the count of a data point known as version which can have around 15 different values. I'm therefore plotting count of version as a series (x axis) within a Bar vertical.

I have my initial x series count plot in Lens using the following formula;

count(kql='general.lastContactTime >= "now-30d" and date_iso >= "now-7d"'

date_iso defines when the data capture occurs,
lastContactTime is used to filter the data and drop stale records.
The above is also configured with a Last 30 days date range.

I'm trying to get a second series on the x axis which picks out the previous weeks data as a comparison.

Something along the lines of

count(kql='general.lastContactTime >= "now-30d" and date_iso >= "(now-7d) -14d"'

But that doesn't appear to be a legal query.
Any chance of some guidance on how to structure the second series?

Hi @Mark_Rodman

have you tried already the shift option?
It would be something like: count(kql='...', shift="2w")

I believe shift requires a time series dataset and also can't be applied to differing series.

Thanks for the suggestion though.

I'm still struggling with this, and I hate to be cheeky, but I don't suppose anyone else has some suggestions.

The shift feature requires the usage of at least one time field in the configuration, not necessarily a dataView with a time field configured.
You could define a Break down dimension with a date histogram based on your time field, collapsed by Sum to hide the actual break down. That should make the shift feature work in the formula.

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.