Looking for help with Batch macros - Monthly sums

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

Two-month summary data.xlsx

Batch Macro

Accepted answers

afv2688

This does the work of summs without the macro

cheers

sumby2month.yxmd

All comments

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!

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703