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

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
#SANTALYTICS

The highly anticipated Alteryx Community tradition is back! We hope you'll join us!

Learn More
We will be upgrading the Gallery to our 2019.4 release this Saturday, December 7th beginning at 9:00am MT. We expect the outage to take last approx. 2.5 hours.
SOLVED

How do I get fiscal month end date for a regular calendar date?

Highlighted
Meteoroid

I'm trying to get the fiscal month end date for dates out of an SAP table. For example, for 4/5/2019 the fiscal period end date is 5/4/2019.  I can see how to get month end, but that doesn't work for what I need. I was able to get week ending date and those dates are below as an example of what I'm working with. 

Nebula
Nebula

Hi there @tlkirby 

 

Can you provide a more detailed formula to calculate the fiscal month end, preferable with a few, unambiguous examples.  In your example,  "4/5/2019(5th of April?) the fiscal period end date is 5/4/2019(May 4th?)" is the fiscal month end always the 4th day of following month?  

 

Dan

 

Meteoroid

Hi! Our fiscal calendar is set up January through December. January has 5 weeks, February and March have 4 and the months repeat in that format.  The last day of January 2019 is Feb 2, February 2019 last day is March 2, March 2019 last day is March 30, and so forth following the 5/4/4 format. Since there is not a specific date that ends the month, this might not be possible. 

Nebula
Nebula

Just to be clear on this point, the week that January 1st falls in is called week 1 and week 5 ends 5 Saturdays after that, Feb 2 in 2019.  Is this correct?

 

Also, your 5/4/4 scheme for 2019 ends on Dec 28.  So Dec 29, 30, 31 are in fiscal Jan 2020?

 

Dan

 

 

Meteoroid

Yes, January 1-5 is week one. For this report, December 29-31 is week 53 of 2019.

Nebula
Nebula

So December 29-31 are in fiscal December 2019?.   And fiscal December 2019 finishes on Dec 31st?

 

Dan

Meteoroid

Yes, December 29 through 30 are Fiscal 19

Nebula
Nebula

Here you go.  It was a tricky one.  Fun, though!

 

WF.png

 

Results, scrolled to show how Feb 01 and 02 are in Fiscal Jan

 

Results.png

 

The work flow only does one year at a time because of the the initial start date.  You can either convert it to a macro and run it dynamically or run it for the next 20 years and store the results in a .yxdb. 

 

Note that your example db only had the Week ending dates in it, so I added a generate rows tool called "Input days" to generate more sample data.

 

Dan

Meteoroid

Thank you so much! I will look at the flow ASAP

Nebula
Nebula

New WF with a small modification.  The original returned the fiscal month but not the end date of the fiscal month

 

WF2.png

 

Dan

Labels