community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Upgrade Alteryx Designer in 10 Steps

Debating whether or not to upgrade to the latest version of Alteryx Designer?

LEARN MORE
SOLVED

Financial year day,week,month columns

Meteoroid

Hello,

 

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.

 

Would appreciate any help, thanks!

Alteryx Certified Partner

Interesting problem, the attached workflow should be a first solution.
Just enter the required Fiscal Year in the Text Input tool and try ...

Meteoroid
Hi there, can't see any attached workflow?
Alteryx Certified Partner

Sorry, this time with attachement.

Meteoroid

Thank you so much for this!

 

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.

 

 

Alteryx Certified Partner

This one should work ... typo within the multi row formula corected
(copy/paste issue).

Meteoroid

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.

Labels