How to Score when you have a Lagged Dependent Variable?

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!

Predictive Analysis

Iterative Macro

Accepted answers

rkapoor

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/341798

https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/How-create-an-Iterative-Macro-The-Collatz-Conjecture-Alteryx/ta-p/108830

Hope this gets you off to a start at least.

Example.yxzp

All comments

rkapoor

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

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703