# Need help calculating weekly averages using either TSVB tables or Scripted fields
**URL:** https://discuss.elastic.co/t/need-help-calculating-weekly-averages-using-either-tsvb-tables-or-scripted-fields/271472
**Category:** Kibana
**Created:** [April 28, 2021, 7:13am UTC](https://discuss.elastic.co/t/need-help-calculating-weekly-averages-using-either-tsvb-tables-or-scripted-fields/271472 "2021-04-28T07:13:07Z")
**Posts on this page:** 11
**Page:** 1
### Author:  [@fidsamurai](https://discuss.elastic.co/u/fidsamurai)
#### Post date: [April 28, 2021, 7:13am UTC](https://discuss.elastic.co/t/need-help-calculating-weekly-averages-using-either-tsvb-tables-or-scripted-fields/271472/1 "2021-04-28T07:13:07Z")
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.
---
### Author:  [@dosant](https://discuss.elastic.co/u/dosant)
#### Post date: [April 28, 2021, 11:43am UTC](https://discuss.elastic.co/t/need-help-calculating-weekly-averages-using-either-tsvb-tables-or-scripted-fields/271472/2 "2021-04-28T11:43:58Z")
@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?
---
### Author:  [@fidsamurai](https://discuss.elastic.co/u/fidsamurai)
#### Post date: [April 28, 2021, 11:47am UTC](https://discuss.elastic.co/t/need-help-calculating-weekly-averages-using-either-tsvb-tables-or-scripted-fields/271472/3 "2021-04-28T11:47:25Z")
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?
---
### Author:  [@dosant](https://discuss.elastic.co/u/dosant)
#### Post date: [April 28, 2021, 11:52am UTC](https://discuss.elastic.co/t/need-help-calculating-weekly-averages-using-either-tsvb-tables-or-scripted-fields/271472/4 "2021-04-28T11:52:50Z")
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
1. then I picked a visualization type (table)
2. 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](https://www.elastic.co/guide/en/kibana/current/lens.html)**
---
### Author:  [@fidsamurai](https://discuss.elastic.co/u/fidsamurai)
#### Post date: [April 29, 2021, 4:41am UTC](https://discuss.elastic.co/t/need-help-calculating-weekly-averages-using-either-tsvb-tables-or-scripted-fields/271472/5 "2021-04-29T04:41:09Z")
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
---
### Author:  [@dosant](https://discuss.elastic.co/u/dosant)
#### Post date: [April 29, 2021, 11:09am UTC](https://discuss.elastic.co/t/need-help-calculating-weekly-averages-using-either-tsvb-tables-or-scripted-fields/271472/6 "2021-04-29T11:09:11Z")
> 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](https://discuss.elastic.co/t/moving-average-visualization/217793/2)
---
### Author:  [@fidsamurai](https://discuss.elastic.co/u/fidsamurai)
#### Post date: [April 29, 2021, 11:18am UTC](https://discuss.elastic.co/t/need-help-calculating-weekly-averages-using-either-tsvb-tables-or-scripted-fields/271472/7 "2021-04-29T11:18:31Z")
Thanks a lot @dosant !!!
Will try and let you know.
---
### Author:  [@fidsamurai](https://discuss.elastic.co/u/fidsamurai)
#### Post date: [April 29, 2021, 12:33pm UTC](https://discuss.elastic.co/t/need-help-calculating-weekly-averages-using-either-tsvb-tables-or-scripted-fields/271472/8 "2021-04-29T12:33:58Z")
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
```auto
{
"window":7
}
```
However I just get an [essages] error and no new calculations are done.
---
### Author:  [@dosant](https://discuss.elastic.co/u/dosant)
#### Post date: [April 29, 2021, 12:51pm UTC](https://discuss.elastic.co/t/need-help-calculating-weekly-averages-using-either-tsvb-tables-or-scripted-fields/271472/9 "2021-04-29T12:51:15Z")
I just realized that this is possible in Lens since 7.12.0 🥳
use moving average function:
---
### Author:  [@fidsamurai](https://discuss.elastic.co/u/fidsamurai)
#### Post date: [April 30, 2021, 4:29am UTC](https://discuss.elastic.co/t/need-help-calculating-weekly-averages-using-either-tsvb-tables-or-scripted-fields/271472/10 "2021-04-30T04:29:35Z")
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.
---
### Author:  [@system](https://discuss.elastic.co/u/system)
#### Post date: [May 28, 2021, 4:30am UTC](https://discuss.elastic.co/t/need-help-calculating-weekly-averages-using-either-tsvb-tables-or-scripted-fields/271472/11 "2021-05-28T04:30:07Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.