topic Re: How to fill missing values in a time range in Reporting

How to fill missing values in a time range?

prithvi — Fri, 25 Sep 2020 21:04:32 GMT

Is it possible to populate the intermediate missing data along the "Target" column in a continuous fashion over the period of time based on the start and end values

_time	Total	Target
2020-08-01		600
2020-09-15	1000
2020-09-16	1250
2020-09-17	1500
2020-09-18	1750
2020-09-19	2000
2020-09-20	2250
2020-09-21	2750
2020-09-22	3250
2020-10-05		5500

Re: How to fill missing values in a time range

ITWhisperer — Tue, 22 Sep 2020 11:48:35 GMT

| timechart span=1d ... | fillnull value=0 Target Total

Timechart will give you continuous time bins, fillnull will allow you to set the value you want in a field if it is not present

Re: How to fill missing values in a time range

prithvi — Tue, 22 Sep 2020 11:53:52 GMT

But this only allows us to fill a constant value rite ? .... Whereas what I want to fill is with dynamic progressive values starting from the given input (here it's 600)

Re: How to fill missing values in a time range

yoho — Wed, 25 Aug 2021 12:27:52 GMT

use streamstats

Re: How to fill missing values in a time range

ITWhisperer — Wed, 25 Aug 2021 12:54:38 GMT

| makeresults | eval _raw="_time,Total,Target 2020-08-01,,600 2020-09-15,1000 2020-09-16,1250 2020-09-17,1500 2020-09-18,1750 2020-09-19,2000 2020-09-20,2250 2020-09-21,2750 2020-09-22,3250 2020-10-05,,5500" | multikv forceheader=1 | eval _time=strptime(time,"%Y-%m-%d") | table _time Total Target | eventstats min(Target) as Min max(Target) as Max min(_time) as Start max(_time) as End | eval days=floor((_time-Start)/(60*60*24)) | eventstats max(days) as period | eval Target=Min+((Max-Min)*days/period) | table _time Total Target