community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Upgrade Alteryx Designer in 10 Steps

Debating whether or not to upgrade to the latest version of Alteryx Designer?

LEARN MORE

Repeatable Workflow Help

Asteroid

Hi Alteryx Community!

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.

 

Workflow and sample data are attached. 

 

Many thanks

 

 

DateVariable1Adstock 20%Variable2Adstock 70%
01 January 2017101000
02 January 20171113100100
03 January 20171012.6150220
04 January 20171214.52120274
05 January 20171214.904140331.8
06 January 20171517.98080232.26
07 January 201703.596160162.582
08 January 201700.7192320113.8074
09 January 201700.1438464079.66518
10 January 201700.02876928055.765626
11 January 201700.005753856039.0359382
12 January 201700.001150771027.32515674
13 January 201700.000230154019.12760972
14 January 201704.60308E-05013.3893268
15 January 201709.20617E-0609.372528762
Bolide
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.
Asteroid

Thanks Josh,

 

Yes, all history to be included.

 

I am a bit lost in some of your suggestions, especially "reverse sort by date"?

 

Thanks

 

 

I think he meant descending order of sort

@BarleyCorn, can you please share the data set that you have in your workflow and share a bit more how it is going to change over time? 

Asteroid

Hi Alexey,

 

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?)

 

Hope this is clear? Have attached all files.

 

Thanks

  

 

 

Labels