This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
Please find the attached Excel workbook displaying the desired output.
I'm looking to create automated columns from formulae on a date field [Date] that buckets them into day, week & month for the current year ([Day], [Week], [Month]) and previous financial year ([LY Day], [LY Month], [LY Year]) starting on the 1st of August of each year, with all cells being zero if they sit outside these windows.
There's also the nuance of the week number being > 0 even if a particular date from [Date] sits outside the financial year, if the first date of the financial year (ie. Aug-01) is in the same week but not on Monday. So for instance for the financial year starting Aug-01 2018, which is a Wednesday, we should have the week number for Jul-30 & Jul-31 be equal to 1, even though they sit outside the financial year.
This process should obviously be robust for leap years.
Just seems to be an issue with the LY Week column where the count starts a few days too early (26/07/17).
I've attached an Excel workbook (desired output left, alteryx output right) which highlights the discrepancies to make it super clear, any suggestions on how to amend this? I'm fairly new to multi-row formulae so can't quite spot how to robustly correct the issue in the icon.
Amazing, thanks so much! I've used the workflow to output a mapping table and joined the output into other relevant workflows, if anyone's interested. It's not as straightforward to paste it into existing workflows.