Need help calculating weekly averages using either TSVB tables or Scripted fields

[fidsamurai (Firdaus Aga)]

I have an index with the following structure -
@timestamp the format is Apr 25, 2021 @ 05:30:00.000,
all of the documents have this same time however the dates vary eg. Apr 26, 2021 @ 05:30:00.000
The next field is a long eg. 134,960, incidentally I need to use the average metric to get the data

What I would like to do is calculate weekly averages, however I can't figure out how to do the same using TSVB tables or scripted fields.

I need help with date math to add the values of the data field for the corresponding last 7 days,
and then divide the same by 7.

Please help.

[dosant (Anton Dosov)]

@fidsamurai, Hi, could you please elaborate why do you look for the solution with TSVB tables and scripted fields?

If you are looking for weekly averages, would date histogram aggregation with weekly interval and avg metric work?

[fidsamurai (Firdaus Aga)]

Hi @dosant,

The basic objective is to have this data displayed in a meeting(preferably in a table), so as long as your solution allows us to do the same we have no issues trying it.

Could you explain how to achieve date histogram aggregation?

[dosant (Anton Dosov)]

Example I have is from Lens editor (because it is the most simple way to visualize data in Kibana):

1. I used two fields from my sample data:

• order_data
• price

2. then I picked a visualization type (table)
3. In the right panel order_date is configured as a date_histogram with a week interval. Price is configured as average

Lens | Kibana Guide [7.12] | Elastic

[fidsamurai (Firdaus Aga)]

Hi @dosant,

I tried your method and it works perfectly, I'll submit it to the team and see if they agree.

As an enhancement to this, would it be possible to generate an average for the past 7 days for each and every date?

Thanks for the steps with Lens!!

P.S. Also replicated it in a Data Table

[dosant (Anton Dosov)]

As an enhancement to this, would it be possible to generate an average for the past 7 days for each and every date?

For such visualizations, you should use TSVB (Moving average aggregation) ,
example: Moving Average Visualization - #2 by nickpeihl

[fidsamurai (Firdaus Aga)]

Thanks a lot @dosant !!!
Will try and let you know.

[fidsamurai (Firdaus Aga)]

Okay so I tried the Moving average in TSVB and it worked for the time series.
But I'm not able to get it working for TSVB tables.

Next I tried it in a data table and while it works I can't define the window.
I tried the json input like in the link you gave me

{
"window":7
}

However I just get an [essages] error and no new calculations are done.

[dosant (Anton Dosov)]

I just realized that this is possible in Lens since 7.12.0

use moving average function:

[fidsamurai (Firdaus Aga)]

Ah! Unfortunately we're on 7.8.
I can use a moving average in the Data Table Visualization, however I can't seem to set the window using advanced input.