A while ago I suggested adding YTD and PYTD flags to the 7.CalendarGen.qvs script:
if(YearToDate([$(vL.DateFieldLinkName)], 0, 4, $(vL.CalendarGenToday)),1,0) AS [$(vL.CalendarTableName) Fiscal YTD Flag],
if(YearToDate([$(vL.DateFieldLinkName)], -1, 4, $(vL.CalendarGenToday)),1,0) AS [$(vL.CalendarTableName) Fiscal PYTD Flag]
This was added to the script and now appears as:
if(YearToDate([$(vL.QDF.DateFieldLinkName_new)], 0, $(vL.QDF.MonthsLeftFiscalDates), $(vL.QDF.CalendarGenToday)),1,0) AS [$(vL.QDF.CalendarTableName) Fiscal YTD Flag],
if(YearToDate([$(vL.QDF.DateFieldLinkName_new)], -1, $(vL.QDF.MonthsLeftFiscalDates), $(vL.QDF.CalendarGenToday)),1,0) AS [$(vL.QDF.CalendarTableName) Fiscal PYTD Flag]
However these flags are only returning a "0"
I'm based in the UK, so my month offset is 4 as per the first example. The $(vL.QDF.MonthsLeftFiscalDates) variable I use is normally -3 so could be part of the problem.
Grateful if someone could investigate please.
I had a quick test and it looks great. The only nuance I see is if you specifically wanted a Fiscal Calendar created that ran from Jan - Dec. If you put in 12 there is no issue. However if you put in in -12 then the fiscal year is the previous year. However this scenario does not make sense as the Fiscal Year is the annual year.
I believe the issue is that you are defining the fiscal months as -3. The function expects a positive number for the number of months left in the year. I.e. for a fiscal year starting 1-April the number of months remaining is 9 rather than -3.
The following is the code i used to generate dummy transactions and then create a calendar.
Set vNumberOfYears = 4 ;
RecNo() as TransactionID,
Date(Floor(MakeDate(2018)-365.2424*$(vNumberOfYears)*Rand())) as Date
On a side note the QDF is now on GitHub so if you find bugs or wish to enhance the product you can contribute through here: GitHub - QlikDeploymentFramework/Qlik-Deployment-Framework: QDF Dev version 1.6.5
Thanks for your reply Damian
Unfortunately if you use a positive integer to set the Fiscal Year, then it is a year out, which is why I use -3. If it needs to be positive, then the calculation of Fiscal Year might also need tweaking.
|Date||9 Year||9 Fiscal Year||-3 Year||-3 Fiscal Year|
Also, the Year2Date function expects a firstmonth variable. If $(vL.QDF.MonthsLeftFiscalDates) is used, then this will always be whatever the "remaining months" variable is, so September onwards if 9 is used.
I'll check out GitHub in a bit, I wasn't aware that the QDF had moved there.
Two points - i do think the fiscal code is not correct on further investigation.
However in the UK how do you refer to a Fiscal year? Do you refer to the suffix or prefix year? I.e. Where the actual data is 1-April-2016 we would refer to the Fiscal Year as 2017 as this is when the fiscal year ends. The actual year is 2016.
Looking good for the Fiscal YTD and PYTD flags Damian, it seems to be picking up the correct MonthStart now so thanks!
We should be able to remove 12 months if we want the Fiscal Year, Fiscal QuarterYear and Fiscal MonthYear to be prefix year, unless we add an optional parameter to the function to allow developers to select between suffix or prefix year?
Also, I thought the idea of passing specific min and max dates was supposed to restrict the calendar only to those dates. That did not seem to work for me either, but I think I've traced it back to the following:
e.g. CALL CalendarGen('Date','Master Calendar',9, '01/04/2015','31/03/2017');
Still brings back 4 years worth of dates.
Think this is something to do with lines 73 and 74:
LET vL.QDF.CalendarGenMinDate = Num(Date(Date#('$(vL.QDF.CalendarGenMinDate)','$(vL.QDF.DateFormat)'))) ;
LET vL.QDF.CalendarGenMaxDate = Num(Date(Date#('$(vL.QDF.CalendarGenMaxDate)','$(vL.QDF.DateFormat)'))) ;
LET vL.QDF.CalendarGenMinDate = Num(Date(Date#('$(vL.QDF.CalendarGenMinDateOrg)','$(vL.QDF.DateFormat)'))) ;
LET vL.QDF.CalendarGenMaxDate = Num(Date(Date#('$(vL.QDF.CalendarGenMaxDateOrg)','$(vL.QDF.DateFormat)'))) ;
Good stuff tho, much appreciated.
Great work both Ian and Damian to find and correct these problems. An updated version (same version number) is released: Qlik Deployment Framework Deploy Tool Just point to your current framework and press update.
Thanks Magnus, however there is still an issue where I'd like to use Prefix Year rather than the Suffix Year. If we tweak Damian's code slightly, then the calculation still works for me using negative values, everything works and there is an option for developers to use Prefix or Suffix years.
Could you check this works?
/*Catch scenario where negative number of months provided for FiscalStartMonths*/
if $(vL.QDF.MonthsLeftFiscalDates) < 0 then
vL.QDF.FiscalStartMonth = 13 - (12 + vL.QDF.MonthsLeftFiscalDates);
vL.QDF.FiscalStartMonth = 13 - vL.QDF.MonthsLeftFiscalDates;