Dynamically Calculating Fiscal Month and Year

Fri, 10 Jan 2014 16:35:15 GMT

I have two dates fields, a list in an AcctSetup table and a list in a Bill Table:

FiscalYearBeginDate
for example 7/1/2012, 8/1/2012, 9/1/2012, etc. (AcctSetup Table)
BillStartDate for
example 2/1/2013 (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?

Re: Dynamically Calculating Fiscal Month and Year

Gysbert_Wassenaar — Fri, 10 Jan 2014 16:44:31 GMT

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.

topic Re: Dynamically Calculating Fiscal Month and Year in QlikView

Dynamically Calculating Fiscal Month and Year

Re: Dynamically Calculating Fiscal Month and Year