Have formulas automatically update with current week's data when refreshed

[cassidiecargill]

Hello

I have 4 data sources incoming into my workflow, they are transposed & unioned in order to make them all the same format. Each week, a new week's data comes in. I am having to manually manipulate my %change & weekly average formulas in order to make it capture the current week's data & also manually  add that weeks data to the summarize & select tools. Is there a way to set it up where it auto refreshes the buttons with the new data?

[MarqueeCrew]

@cassidiecargill ,

Yes.  But this takes effort.  It is what should be done for all production work.  If for example the data comes in with a "DATE" that makes things easier.  Sometimes you need to compute to determine what's the newest date.  Transposing data is helpful in comparing this to that because this is always the current row and that is row-1.  There is also a call for using CReW Dynamic Formula to help to calculate the MATH based upon the dates and instead of transforming the data, you could dynamically figure out that it is the change in April over March.  This is a more efficient way of solving the problem.

No.  I don't carry an example of these solutions.  If you simplify your data processing and give sample input Month 1 and updated input Month 2, it is likely that someone will provide you with A solution to help you automate.  It should at least take the pain out of the monthly process.  But do also think about the checks that you should conduct that ensure that besides the math that the data content is consistent with expected results.  If you automate, you might miss glaring issues that don't ERROR your workflow.  

Another tool of choice is DIRECTORY & DYNAMIC INPUTS.  If you can trust the naming of the files (or dates on the delivery of data), you can dynamically receive the data and choose the right inputs for the workflow without having to update those static input tools.  This might also help in the automation.

Cheers,

Mark

[DavidP]

Can you share a workflow with some data? You might have to use a batch macro to replace parts of your workflow.

[danilang · Accepted solution]

Hi @cassidiecargill 

Like @MarqueeCrew said, this will take some work.  

Correct me if I'm wrong, but it looks like the Cross Tab tools in the blue rectangle above are where you're generating the weekly columns and your data coming out of the Summarize actually has the weekly data in rows.   It also looks like the streams in the green rectangle are in weekly column format.  If you transpose after the green, you should be able to union with the data coming from the summarize tools in blue.  Apply your formulas here since weekly data will all be in a single column where you can apply filters to act on specific rows.  i.e. current week -1, etc.  You may need to sort and use multi-row formulas if your 3MOWeeklyAverage info is not available on the current row. Then Cross Tab the entire set to your final wide format.  

Dan

[cassidiecargill]

This was very helpful, to remove a lot of manual manipulation & ensure that the columns are added in automatically when data is added. the transform before the sum rather than after was also a good move.

unfortunately, the data at the top is coming from SAP BOA queries, so it is not a clean input. 

i appreciate all of the help, team!

[Gina2021]

Hi @cassidiecargill ! I'm at the beginning of automating a similar weekly process. Do you happen to still have this workflow that you could post as an example?  Thanks much! Gina