Dynamically creating formulaic columns

[bryanbumgardner]

Hello everyone,

What I'm trying to create is a workflow that can run quality assurance tests on data. Essentially, I need to see if the data for a specific day has changed more than 10 percent than the day prior. Right now each column is a day, but the way the workflow operates, the user could select as many days as they desired. The data looks like this:

Right now only one day is present, but in the future users might select ranges of five to seven days. I want, after each date column, for there to be a column that shows the percent change from the prior day, but I can't figure out how to add this dynamically while also keeping the date in the column name.

I've tried using an iterative macro but I can't figure out how to get the column names to work - they'd have to be named something static to work properly. 

Any help here is appreciated! This is a challenging problem. 

Thanks,

- Bryan 

[mborriero · Accepted solution]

@bryanbumgardnerassuming that you are getting the date in columns using a cross-tab tool, find attached a possible solution for your case.

[Inactive User]

Transpose the Date Columns so each row is a date. Then, sort the dates in ascending order. Then, insert a record ID tool and then a multi row formula tool with the formula:

New Field - [Change]:

IF [Record ID]=1 THEN 0 ELSEIF [Strategy]=[Row-1: Strategy] AND [Campaign]=[Row -1: Campaign] AND [Targeting Container Name]=[Row -1: Targeting Container Name] THEN ABS(1 -  ([Value]/[Row-1: Value])) ELSE 0 ENDIF

This will give you the absolute percentage, in decimal, change. Then you can filter/etc. whatever you need to do then. If you need it in columns then just cross-tab it back to columns.

[suli]

Hi @bryanbumgardner

I think it is an ideal case for 'Dynamic Replace' tool.

It is a bit tricky to get a correct input data structure, but once you set it up once will work well!

Please find attached sample workflow!