Notes:
The brief said "your solution should be flexible and include flow variables for both the start and the end dates of the fiscal year. ". I have chosen to keep the parameters of a fiscal year in an excel file (which is read by the Excel Reader node). You can put what ever dates and any number of fiscal years you want - the flow will adapt.
Questions:
It took me 3 nodes to get from String: 11/12/2013 to Local Date 2013-12-11. Can anyone please tell me how I can shorten this? I just couldn’t do it (much to my frustration!)
Definitely not the shortest workflow, but it gets the job done!
All the “Custom Fiscal Year” components add in the missing fiscal years from the table.
Looking forward to how other people made theirs shorter!
Hi there Frank! Yes, sometimes dealing with dates gives you a headache!
If you want to change your locale, you should use the Locale parameter. See the image, with one node, you can convert to date and change the Locale, in this case I’ve used mine which is ES-PY (yyyy-mm-dd)
Morning y’all!
Here’s my solution. A rather naive approach after seeing others more general solutions. But I think it does the trick. Just a reminder, be careful to use a joiner in the final step here! It could duplicate your initial rows (we have more than one record per date in some cases). Interesting to see how each one have different solutions for the same task.
Months are represented with uppercase M, not lowercase. That is because lowercase m is used for minutes. So your proper format is yyyy-MM-dd for your locale.
The other thing that I noticed from your screenshot, though it worked, the proper format of the dates from the file is dd/MM/yyyy as opposed to what you are using in your screenshot. Basically 03 would be dd or MM, while 3 would be d or M. The file contains dates such as 07/02/2013 or 03/08/2012, etc.
I made Fiscal date start and duration separate to populate default and minimum values. I reality I would probably read minimum and maximum values from the input table directly. anyways
I guess that all people think differently. I read between the lines that we can/should/must build a fiscal calendar from the scratch. So my workflow is designed to have two inputs: start date and number of years to build the fiscal calendar and join it with the input dataset using python code (pandas/pandasql should be installed). KnimeIT_5.knwf (48.0 KB)
Proposed solution adopts 2 approaches:
Approach 1. Based on the FY list provided - only reports identified FY’s.
Approach 2. Based on a component configured by the user - based on a generic FY definition, extrapolates the applicable FY’s for the dates in scope.
Instead of extrapolating the incomplete lookup table I went with a different route: The first row of the lookup table is turned into Flow Variables and from there the fiscal year is calculated with a Java Snippet. This is possible because fiscal periods usually don’t change.
The idea of using Calendar objects turned out more bloated than I expected, but comparing days, months and years in if-else constructs felt wrong. The same logic has also been implemented without code using the Date&Time Difference node.
As always on Tuesdays, here’s our solution to this week’s challenge! Very similar to some of the solutions you folks posted – but not to many, which is very cool! We’re again impressed with how y’all think outside the box when approaching these challenges!