Skip to main content
Announcements
See why Qlik was named a Leader in the 2024 Gartner® Magic Quadrant™ for Data Integration Tools for the ninth year in a row: Get the report
cancel
Showing results for 
Search instead for 
Did you mean: 
qliknerd
Contributor III
Contributor III

Bug in Calendar Script [FIXED]

Hi

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.

Thanks

Ian

1 Solution

Accepted Solutions
Anonymous
Not applicable

Hi Ian,

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.

Damian

View solution in original post

13 Replies
Anonymous
Not applicable

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 ;

Facts:

Load

  RecNo() as TransactionID,

  Date(Floor(MakeDate(2018)-365.2424*$(vNumberOfYears)*Rand())) as Date

  Autogenerate 1000;

   

CALL CalendarGen('Date','Transaction',9);

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

qliknerd
Contributor III
Contributor III
Author

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.

http://www.calendarpedia.co.uk/download/financial/financial-calendar-2016-2017-landscape-days-aligne...

   

Date9 Year9 Fiscal Year-3 Year-3 Fiscal Year
31/03/20162016201620162015
01/04/20162016201720162016

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.

Kind regards

Ian

Anonymous
Not applicable

Hi Ian,

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.

qliknerd
Contributor III
Contributor III
Author

In the UK, we'd refer to the prefix year, so 1-April-2016 is in Fiscal Year 2016/17

Anonymous
Not applicable

Hi Ian,

I have created a fork in GitHub. I think this is now working correctly. Can you have a look?

GitHub - djwaldo/Qlik-Deployment-Framework at patch-2

qliknerd
Contributor III
Contributor III
Author

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)'))) ;

should be:

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.

Magnus_Berg
Employee
Employee

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.

Regards

Magnus

qliknerd
Contributor III
Contributor III
Author

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);
Else
vL.QDF.FiscalStartMonth = 13 - vL.QDF.MonthsLeftFiscalDates;
endif;

CALL CalendarGen('Date','PrefixCalendar','-3');
CALL CalendarGen('Date','SuffixCalendar','9');

Anonymous
Not applicable

Hi Ian,

can you send me a link to a fork in GitHub and I will test it.