Qlik Community

Qlik Deployment Framework

This group has been created to host, centralize and collect everything related to the Qlik Deployment Framework (QDF). QDF provides a set of best practices, libraries and utilities that facilitate the recommended setup and management of QlikView and Qlik Sense environments. Please note that an understanding of the Qlik platform is recommended before joining this group.

Highlighted
qliknerd
New 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

Tags (1)
1 Solution

Accepted Solutions
Employee
Employee

Re: Bug in Calendar Script

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

13 Replies
Employee
Employee

Re: Bug in Calendar Script

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
New Contributor III

Re: Bug in Calendar Script

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

Employee
Employee

Re: Bug in Calendar Script

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
New Contributor III

Re: Bug in Calendar Script

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

Employee
Employee

Re: Bug in Calendar Script

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
New Contributor III

Re: Bug in Calendar Script

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.

Employee
Employee

Re: Bug in Calendar Script

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
New Contributor III

Re: Bug in Calendar Script

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

Employee
Employee

Re: Bug in Calendar Script

Hi Ian,

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