topic Re: Bug in Calendar Script in Archived Groups

Bug in Calendar Script [FIXED]

qliknerd — Tue, 22 Jul 2025 17:59:49 GMT

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

Re: Bug in Calendar Script

Anonymous — Tue, 08 Nov 2016 19:13:50 GMT

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

Re: Bug in Calendar Script

qliknerd — Wed, 09 Nov 2016 09:16:48 GMT

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-aligned.pdf

Date	9 Year	9 Fiscal Year	-3 Year	-3 Fiscal Year
31/03/2016	2016	2016	2016	2015
01/04/2016	2016	2017	2016	2016

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

Re: Bug in Calendar Script

Anonymous — Wed, 09 Nov 2016 19:57:30 GMT

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.

Re: Bug in Calendar Script

qliknerd — Wed, 09 Nov 2016 21:11:28 GMT

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

Re: Bug in Calendar Script

Anonymous — Wed, 09 Nov 2016 21:44:45 GMT

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

Re: Bug in Calendar Script

qliknerd — Wed, 09 Nov 2016 22:51:39 GMT

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.

Re: Bug in Calendar Script

Magnus_Berg — Thu, 10 Nov 2016 08:31:43 GMT

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

Re: Bug in Calendar Script

qliknerd — Sat, 12 Nov 2016 17:42:36 GMT

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

Re: Bug in Calendar Script

Anonymous — Tue, 15 Nov 2016 11:04:10 GMT

Hi Ian,

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

Re: Bug in Calendar Script

qliknerd — Tue, 15 Nov 2016 12:56:26 GMT

Hey Damian

Here you go: Qlik-Deployment-Framework/7.CalendarGen.qvs at patch-2 · iPFT/Qlik-Deployment-Framework · GitHub

Thanks

Ian

Re: Bug in Calendar Script

Anonymous — Wed, 16 Nov 2016 20:25:23 GMT

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

Re: Bug in Calendar Script

Magnus_Berg — Thu, 17 Nov 2016 08:56:36 GMT

Hi Ian, the fix is approved and committed. Have also updated the deploy package:Qlik Deployment Framework Deploy Tool

Best regards

Magnus

Re: Bug in Calendar Script

qliknerd — Thu, 17 Nov 2016 09:19:06 GMT

Magnus, great news thank you. I'll update out Development Env today and do some testing.

Damian, thank you too for applying the initial fix and for your promptness.

Have a good day all

Kind Rgds

Ian