Estimating Remaining Days

[sshutchinson]

In my data set I have data from Dec. 1-28th in column header "Date". Because there are 31 days in Dec., I need to estimate the rest of the data for the following dates: 29th, 30th, and 31st. To do so, I want to use a 7 day lookback. By that I mean, the 29th will use data from the 22nd. The 30th will use the data from the 23rd. The 31st will use data from the 24th. 

[jdminton]

Here is how I would do this. I made an assumption that you needed to forecast based on the other fields (i.e. y/n, sold/unsold) as well. 

 

[sshutchinson]

Hey, thanks for this. Wondering how I could still include 12/22, 12/23, and 12/24 in the output. Right now they are not included

[jdminton]

Apologies, change the union input for #2 as shown here:

 

[sshutchinson]

That makes more sense, thanks!
Were you getting an error on the "Generate Rows" tool? My workflow is saying 'The field "RowCount" is missing. Compare the tool configuration with the input stream.' ?

[jdminton]

Check your configuration. Row Count is the default name, but I've changed:

 

[sshutchinson]

Cool, I missed that bottom section. Thanks for pointing that out.

Wondering if we could tweak this a little. Right now the group by section taking the sum of the total. Instead I want that Raw Data to remain the same and just add the estimated days to the Raw Data. The estimated days would still be gathered by doing the 7 day look back. Does that make sense?

[jdminton]

So essentially, I just moved the union to pull in your original data and add the new. The newly added are still summarized as joining would end up creating duplicates otherwise. I think this is what you're looking for, but let me know if it isn't.

 

[sshutchinson]

Hi Bolide, 

So the output looks good in terms of just adding to the existing raw data, except the data that is added is only 4 rows. Could you help me make it so that the entire target day is added? For example, 29th uses the 22nd as its 7 day look back estimation day. In this case we would see 137 rows of the exact same data from the 22nd used for the 29th. 

For the 30th, we will see 126 rows with the date 23rd be used for the 30th. 

Does this make sense?

[jdminton]

I guess there is a reason that you are trying to duplicate all of the transactions individually. For forecasting, I would typically not need them. See the "New Method" container for the solution.