Bug in Calendar Script [FIXED]

[qliknerd]

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

[Anonymous]

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]

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

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

[Anonymous]

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]

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

[Anonymous]

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]

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]

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]

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]

Hi Ian,

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