How can I aggregate or group monetary transactions on the next business day

lsojat

I need to find a way to group or aggregate transaction amounts done on Saturday, and Sunday (or on a holiday) to show their total on the next business day, usually Monday. I have a list of transactions by account number and I need to aggregate the transaction totals for each business day, grouping them by date seems easy enough but how can I handle the weekends when I need to group Saturday and Sunday with the Monday totals? Any help would be greatly appreciated I work in DPS (Monarch Data Prep Studio)

CPorthouse

One possibility is to look at the date in question and see if it is a Saturday or Sunday and then adjust that date one or two days into the future using a nested if statement. For example,

if(weekday(added)=1,DateAdjust(added,0,0,1),
if(weekday(added)=7,DateAdjust(added,0,0,2),added))

Where "added" is a DateTime column. The weekday function will return a value between 1 and 7, where 1 is Sunday (by default but can be changed) and Saturday=6.

You can then aggregate on the new formula column.