Looking for help with Batch macros - Monthly sums

[jrpaul]

Hi,

I'm new to Alteryx, but have managed to get my large workflows implemented, except for a couple parts that I was using Tableau Prep for.  I started watching some videos on Macros, but learn better by example, so I'm hoping someone can assist me.  Essentially I have a large data set of visits by date, customer, visit type.  It is updated monthly.   I want to calculate 3-mo, 6-mo and/or 12-mo summaries for some of the visit types by customer and reporting period end date.   This needs to be run each month on all records going back to October 2017, as some historical data can get updated and ID's may change.    I know how to create formulas for calculating time diff between the visit date (month) and the reporting period month.  Less sure about how to build into a macro to repeat the calculation each month, and whether I need to supply a table of end dates (months), since the data may not contain the last day of a given month.   The output is organized by last day of month (reporting period end).  

I attached a small data file looking for 2-mo totals, which I can extend to my larger data set if someone can assist with this example.   Thanks!

Jeff

[afv2688]

Hi @jrpaul ,

If you need to run your calculations from the current date to october 2017 I would recommend you to use in your macro the datetimenow function. It gives you the current date which you can use in conjunction with DateTimeMonth(dt) to select the current month to perform the calculation.

There are many datetimefunctions which you can use to perform your calculations, like DateTimeLastOfMonth() to be able to know the last date of the current month (if needed).

I don't know it this will help you. If not, let me know and will assist you as much as possible. :)

cheers

[jrpaul]

Thanks. I have used a few of the datetime functions in other calculations.  For this data set, the data doesn't go to the current date (monthly data with a lag), it goes through end of prior month, though I could use current date to calculate this (assuming the data never lags by more time).  

Being new to macros, I'm less certain of how to construct the batch macro for the situation I described (calculating 2-mo sums for each end of month period).  Been watching a few videos and will make some attempts, but if you could assist with creating a macro workflow with the sample data, that would give me something to walk through and mimic on my larger data set. 

Thanks again!

[afv2688 · Accepted solution]

This does the work of summs without the macro

cheers

[jrpaul]

Thanks, that works and I can extend it to do 6-mo sums on my 100M rows of data.  I hadn't gotten to the multi-row formula tool yet.  Just have to make sure the data is sorted in the correct order, right?   Looks similar to table calcs in Tableau, but easier to follow and more flexible.  I'll have to spend more time on that tool and the Crosstab/Transpose tools.

I do want to learn about Batch Macros, so if there's a way to solve this with that tool, I'm still interested in that. 

Thanks again!