Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Dynamically Calculating Fiscal Month and Year

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

 

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

1 Reply
Gysbert_Wassenaar

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.


talk is cheap, supply exceeds demand