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 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).
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
Apache, Apache Lucene, Apache Hadoop, Hadoop, HDFS and the yellow elephant
logo are trademarks of the
Apache Software Foundation
in the United States and/or other countries.