Kibana Use Date Interval without using Date Histogram

[BenB196]

Hi All,

I'm trying to create a visualization, where I have a date field as the columns of a table. I want to have the columns be by week, but don't want to use a date histogram, and instead want to use the dates as intervals with the complete time series.

Is there a way to achieve this without adding an additional field that adds the date by week as a keyword?

[flash1293 (Joe Reuter)]

Why do you not want to use date histogram? You can customize the interval used for the date histogram to use weekly buckets.

I'm not sure what you mean by

use the dates as intervals with the complete time series.

Can you explain how your data looks like and how you want the table to look like?

[BenB196]

@flash1293 yeah, I don't think I explained my question very well, so let me try again.

I have data which spans about 2 years (and growing). This data has two fields, office_name and number_of_users. I want to perform a cumulative sum on the with the entire data range (2 years) by week, but I only want the table to show the last 8 weeks.

Table should look like this:

| Office Name | 2021-06-06 | 2021-06-13 | etc... |
| Office 1    | cumul. sum | cumul. sum | etc... |
| Office 2    | cumul. sum | cumul. sum | etc... |

From what I can tell currently, is that this can only be partially achieved. Either I set the date histogram to 8 weeks (but then my cumulative sum will be off, because it doesn't take into account data before the 8 weeks), or I set the date range to be 2 years, but then have a table with 104 columns (weeks) in it. Another issue with setting the date range to 2 years, is that the data is constantly going so will require constant updating of the time range (while I could set it to something large like 10 years, it wouldn't solve the problem of only wanting to see the last 8 columns (weeks).

[flash1293 (Joe Reuter)]

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 timestamp):
• Label this week, KQL: *
• Label - 1 week, KQL: timestamp < now-1w
• Label - 2 weeks, KQL: timestamp < now-2w
• ...

Note: 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 1w.

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