Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have two dates fields, a list in an AcctSetup table and a list in a Bill Table:
I load my AcctSetup Table:
AcctNum
FiscalYearBeginDate
I load my Bill Table
AcctNum
BillStartDate
What I need to know is the Fiscal Month and Fiscal Year for the BillStartDate. I'll probably put this in a Load Script to perhaps add those two additional fields to the Bill Table:
If FiscalYearBeginDate = 7/1/2012 then BillStartDate 2/1/2013 is Month 7 of Fiscal Year 2012, BillStartDate 8/1/2012 is Month 6 of Fiscal Year 2012, 9/1/2012 is Month 5 of Fiscal Year 2012, etc. So for each BillStartDate I need to know which month and fiscal year that date falls into.
Any suggestions on what the calcs should be in the load script for this?
See this blog post: Fiscal Year
Also see this discussion for the creation of a master calendar: Tutorial - Using Common Date Dimensions and Shared Calendars. The qvc library used in the tutorial can create fiscal calendars starting at any month you like.