Let’s talk Alteryx Copilot. Join the live AMA event to connect with the Alteryx team, ask questions, and hear how others are exploring what Copilot can do. Have Copilot questions? Ask here!
Start Free Trial

Alteryx Designer Desktop Discussions

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

Is it possible to dynamically change the range when overwriting?

Aviator0807
8 - Asteroid

So for example lets say I am reporting weekly forecasts on a 3 month cycle ( right now its Sept, Oct, Nov). I export and write  to an excel sheet. Now when a new month starts (October), I want to report on the previous month(Sept) one last time and append a new month (December) to keep the 3 month cycle. I want to be able to Overwrite what is in the table (excel) for September (as now these turn from forecasts to actuals),October, and November and then append the forecasted numbers for new month of December.

 

Currently I just copy and paste the output from Alteryx over to the excel but I would like to automate this, but I am unsure about how to deal with this scenario. The problem I am facing is that when overwriting you have to specify a range you want to overwrite but in my case that range I want to overwrite changes for every new month (i.e a new month is introduced thus making the excel longer changing the range). 

 

So this is what is looks like for the last week of September.  Although there will be more rows for different stores etc.

MonthYearSales 
September2021240
October2021250
November2021300

 

Now when I run it again first week of October I would like it to overwrite September, October, November and Append December Numbers.

MonthYearSales 
September2021280
October2021300
November2021350
December 2021400

 

For the first week of every new month I would like to overwrite the previous month and the 2 other reported months then also append the new month. Then continue overwriting all 3 months until the first week of the next month in which October will be overwritten one last time and then January 2022 will be added so then I will be reporting on (Nov, Dec, Jan)

3 REPLIES 3
Maskell_Rascal
13 - Pulsar

Hi @Aviator0807 

 

In the Output tool you can select a range to write to, which I'm assuming you already knew. What's more interesting is that you can select a much longer range and the tool will overwrite all those cells even if there is no data from Alteryx. 

 

In this example, I selected range of A1:C1000, even though my data will only be a max of 3-4 rows. 

Maskell_Rascal_0-1632768869114.png

 

The file I'm outputting to already has 4 rows of data, but my incoming data will only have 3 rows. 

 

Current file:

Maskell_Rascal_1-1632768929525.png

 

Incoming data:

Maskell_Rascal_2-1632768974889.png

 

Output after running workflow:

Maskell_Rascal_3-1632769006261.png

 

Let me know if that works for you. 

 

Cheers!

Phil

Aviator0807
8 - Asteroid

@Maskell_Rascal Yeah that's one solution I was thinking of, only problem with that is I would have to re-calculate all months every week indefinitely. Like Sept. should be done next week and never touched again. But if I do it the way you are suggesting I will have to run it again in October and November and December..etc or else it will get lost. Same with Oct once we hit November and so on. 

Maskell_Rascal
13 - Pulsar

@Aviator0807 - can you share part of your workflow with some sample data and the calculations in question?

Labels
Top Solution Authors