Hi,
I'm new to Alteryx and to the community page, so apologies for any stupid questions.
I work with financial data and I'm trying to get data to be available for everyday of the year by 'rolling' data over non-working days.
I'm trying to get the the last avaliable day's data to roll over both weekends and bank holidays.
I have a calendar mapping file which I have managed to create. With this I have a flow which rolls Friday data over weekends (Saturday and Sunday) by using a formula tool followed by a text to columns tool.
Example,
Input:
Date | Day | P&L | Formula |
01/01/21 | Friday | $10 |
I have used a formula tool IIF ( (Day) = 'Friday' bring back 'Friday,Saturday,Sunday' else (Day) )
Date | Day | P&L | Formula |
01/01/21 | Friday | $10 | Friday, Saturday, Sunday |
Then using the text to columns tool and the formula tool to bring back the following
Date | Day (No longer in use) | P&L | Formula (In use) |
01/01/21 | Friday | $10 | Friday |
02/01/21 | Friday | $10 | Saturday |
03/01/21 | Friday | $10 | Sunday |
This simple solution works to 'roll' data over weekends by duplicating the Friday lines.
However, I was wondering whether there's is a way I could also capture bank holidays using this logic?
e.g for the 2020 Christmas period, I would need to roll 23rd data for 4 days - over 24th(bank holiday) 25th and 26th (weekends) and the 27th (bank holiday).
Any help would be greatly appreciated
Thanks
@JonTaylor1
Try to make it dynamic as much as possible, and turns out to be a big one..😁