Thanks, that's a great explanation.
Unfortunately I don't think there is a good way at the moment to achieve this. I think the best you can get to is the following:
In Lens, switch to datatable and do a top values of the office name for "rows". For metric, do a sum of number of users. For columns, do the following:
- Do a "filters" function with the following filters (assuming your time field is called
this week, KQL:
- 1 week, KQL:
timestamp < now-1w
- 2 weeks, KQL:
timestamp < now-2w
now-1w will subtract 1 week from the current day and hour, it won't start with the beginning of the week. If you want that instead, you can use
now-1w/w. It's important to not add spaces after
now and before
Add this table to a dashboard and set the time range in the top right to something like "last 10 years" (spanning your whole data range). Save the dashboard with "Store time with dashboard" switch checked, so it will be set automatically when going to the dashboard.
If you have other visualizations on the same dashboard that need the current time range instead of the full data range, you can also overwrite the time range for this panel by setting "Customize time range" in the panel options
This should give you the values you want to see, but with the downside of not getting the resolved dates in the column headers but "-2 weeks" and so on labels