This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
It been a while, but looking for some inspiration!
I have written a workflow to calculate adstock on numerous variables - see below. Repeatability is very important for this WF, and mine is not.
Adstock takes a value from last period, multiplies it by the adstock % and then adds the current period value to the adstock value.
Below is an example, Adstock on variable 1 is last weeks value x .2 plus this weeks value and so on.
Where repeatable bit comes in is that this process needs to be done many times and adstock can change, adstock level on different variables will be different and adstock % can change for each variable. My workflow uses the multi-row formula tool - but each variable needs to be entered separately - which doesn't really save much time from excel.
If I understand your question correctly, are you looking to include all of history in your adstock and not just the previous week? (Like further diminishing the oldest adstock by 20% each week)?
If that's the case, you can reverse sort by date, add a row ID, raise 20% to the power of the record ID, then multiply by that week's variable to calculate adstock. Then summarize all rows to get your total value.
I have progressed a bit with my workflow, but still not there yet - now attached.
I have addressed the need for repeatability by joining two files, AdstockVarTestV1 that has adstock levels (that will change or be different for different models)
and AdstockInputV1, which are the variables to be transformed. Adstock transformation is only needed for some of the variables. The workflow attached needs some kind of "If Then" as the calc is running on to different variables due to the Transpose. (Also, Alteryx is rounding, where I would like transformed variables to the 5th decimal place - have tried all the "Type"s in the Multi-Row Formula tool?)