How to Score when you have a Lagged Dependent Variable?

[AmyL1]

I am trying to use the scoring tool to generate predictions using the model I built with the Linear Regression tool. The problem is, one of my explanatory variables is a lagged dependent variable so I need each new prediction to automatically feed into the lagged dependent variable field for the scoring tool to work

With my input data, I defined by lag field using the Multi-Row Formula tool:

if [RecordID] = 1 then 0.0013 else [Row-1:NLdiff] endif

However, this formula isn't translating forward when new NLdiff values are scored. I'm thinking I have to create a batch macro of some sort but I don't know where to start. Any help is appreciated here!

[RishiK]

Hi there

If you go down the Batch Macro route, you would have to specify a control parameter to run the model > scoring > update the lagged dependent for as part of 1 iteration. What would this be in your data?

[AmyL1]

Not sure if this answers your question, but I want my Lagged DV field to be equal to the Predicted (Scored) DV - 1. So thinking of it in terms of a for loop, LagDV [ i+1 ] = PredictedDV [ i ]

I've never used the Macro Input tool before so I am not sure how to define the control parameter

[RishiK]

Have a look at some examples here:

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Linear-Regression-in-Batch-Macro/td-p/...

If you can provide your workflow with some anonymised data I can help you

[AmyL1]

Thanks for the help here! I have attached a simplified workflow of the problem I'm dealing with. I have to somehow make LagY = [Row-1:Predicted] once the forecasting begins, but I'm not sure how to define that within the scoring tool

[RishiK]

Ok I think I understand but want to clarify. So you want to update LagY everytime a forecast has been produced for a specific record ID with the [Row-1:Predicted]

Do you want to use this again for the forecasting after ie. populated back into the Linear Regression for further forecasts?

[AmyL1]

Yes, so the first Predicted value would be calculated using the actual LagY value, but the remaining Predicted values would be calculated using [Row-1:Predicted] instead of LagY

[RishiK · Accepted solution]

Amy

I've built a skeleton Iterative Macro for you instead of a Batch Macro as I do not have your data or know the format etc.

What the macro does, is run for the first time and update the LagY after the Score.  If LagY = Predicted, then the loop stops, however if LagY ! = Predicted them the loop continues, ie. go back to the Input and process through with the updated LagY value.

If you open the macro up and enable the Interface Designer via View >  Interface Designer, under the Properties of the Macro, I have set the Iteration Output to I Output.

You will need to configure the Input anchor to match the format of your Input data (I recommend creating LagY outside of the macro and defaulting it), as well as configuring the 2 Output anchors.  I Output which will go back into the Input will need to have the same format ie. no of fields/columns as the Input Anchor would expect.

These articles may also help:

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Iterative-Macro-Configuration/td-p/341...

https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/How-create-an-Iterative-Macro-The-C...

Hope this gets you off to a start at least.

[RishiK]

I'm interested to see how this works out so let me know.

[AmyL1]

Thank you so much! That was very helpful and I feel like I understand how to do an iterative macro in Alteryx now. 

The only problem is my Filter tool doesn't recognize [Row-1:Predicted]. This type of syntax only seems to work in the Multi-Row Tool for me (not sure if there's an easy fix for this?)

Anyways, I modified it slightly to account for this by changing the filter to !IsNull([Predicted]) but it didn't work. I'm not sure if I am missing something? The idea in my mind was if the Predicted value is NULL, the I Output would loop it back to the Input tool and re-score until all Predicted values aren't NULL

I'm guessing I'm not configuring my input/output tools correctly. I used a file input for the Input tool that connected to my original data. Maybe this is the problem