Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

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

cassidiecargill
5 - Atom

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?

 

cassidiecargill_0-1581541204879.png

 

5 REPLIES 5
MarqueeCrew
20 - Arcturus
20 - Arcturus

@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

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
DavidP
17 - Castor
17 - Castor

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

danilang
19 - Altair
19 - Altair

Hi @cassidiecargill 

 

Like @MarqueeCrew said, this will take some work.  

j.png

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
5 - Atom

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
8 - Asteroid

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

Labels