use existed before and after data to fill in the blanks

Felix776

Hi Alteryx Users,

I am sure there is a way, but I not able to find it.

As table shows below. I have columns with Dates and Values to start off with.

I have missing data in values. and I want to use the before and after data to join the dots.

For example

I am missing data  for three days. 5-6-7/10/2019, so I take the value from 08/10/2019 (90) and 04/10/2019 (57), do a subtraction and divide by 4 as I have three days with missing values, the result gives in the increment level of (8.25), then I use it to add onto the value of 04/10/2019 (57) to predict the value for 05/10/2019 (62.25) and so on. 

I did the same for 11-12-13-14 /10/2019 but only to divide by 5 as I have 4 days of values missing.

I tried the Imputation tool but doesn't seems to give me what I need. I assumed there is something clear use of predictive tool to get what I want. Any help would be appreciated. 

Dates	values		Predicted Values	Increment Levels	Calculations of increment level
01/10/2019	25
02/10/2019	53
03/10/2019	42
04/10/2019	57
05/10/2019			65.25	8.25	(90-57)/4
06/10/2019			73.5
07/10/2019			81.75
08/10/2019	90
09/10/2019	100
10/10/2019	108
11/10/2019			116.6	8.6	(151-108)/5
12/10/2019			125.2
13/10/2019			133.8
14/10/2019			142.4
15/10/2019	151
16/10/2019	159
17/10/2019	167
18/10/2019	176

RolandSchubert

Hi @Felix776 ,

I think, you only have rows for days with data ... created a sample workflow, that creates missing dates and calculates the average, hope, it is helpful.

Best,

Roland

BeforeAfterAverage.yxmd