bug in CALCDATE

CALCDATE bug report A client wanted Quarter to Date and Year to Date as a columns on an account schedule. We made the modification and tested it, but discovered at the end of the first quarter that the two columns of numbers were different although they should have been the same. We investigated extensively and discovered a bug in the CALCDATE routine if the first parameter is “CQ-1Q+1D”. We have written a codeunit and associated table that demonstrates the problem and we have also written a fix if that we will share if anybody is interested. Our demonstration table has six columns. The first column is “refdate” which is the date that is passed as the second argument to CALCDATE. The other five columns are ‘CQ’, ‘CQ-1Q’, ‘CQ-1Q+1D’, ‘-CQ’, and ‘-CQ+1D’. These columns match the strings that are passed to CALCDATE as the first argument. The codeunit simply runs refdate from 01/01/2000 to 12/31/2001 and fills in all of the columns for each refdate. (Note that the -CQ+1D column is not of any particular interest. We did that column at the same time we did all the other columns as part of our trouble shooting and fix design.) Looking at the resulting table, the first quarter is correct. CQ is 03/31/2000, CQ-1Q is 12/31/1999, CQ-1Q+1D is 01/01/2000, -CQ is 01/01/2000 and finally -CQ+1D is 01/02/2000. But when refdate moves into the second quarter of 2000, we see a problem. The entries when refdate is in the range of 04/01/2000 through 06/30/2000 are: CQ: 06/30/2000 (correct) CQ-1Q: 03/30/2000 (incorrect – 03/31/2000 is the correct answer) CQ-1Q+1D: 03/31/2000 (also incorrect – 04/01/2000 is the correct answer) -CQ: 04/01/2000 (correct) -CQ+1D: 04/02/2000 (correct) This same symptom happens in other quarters as well. This bug was first discovered in Version 2.00C, but it still exists in Version 2.60B. If you are interested we will send you an fob file that contains two objects, a table and a codeunit. With the table empty, select the codeunit and hit run. Then you can then look at the data in the table. Weston Weston M. Binford, Jr. President Trusted Business Information Systems, Inc. weston@trustedbis.com Weston M. Binford, Jr weston@trustedbis.com

Hi Weston! Does “-CQ - 1D” give a correct answer (I would expect it to equal the last day of the previous quarter)? And 2000 was an unusual leap year. Do the same results apply to 1999 and 2001? cheers,

The problem with -1M or -1Q is that nobody knows how long (in days) a month or a quarter is. If calculate starting May 17th, then CALCDATE(’-1M’, 17.5.01) will give the correct answer 17.4.01. The 17th of the month before. Same applies for -1Q which is defined as “minus three months”: CALCDATE(’-1Q’,17.5.01) → 17.2.01 which is correct Therefore CQ-1Q: 03/30/2000 CALCDATE(’-1Q’,30.6.2000) → 30.03.2000 is also correct. In fact -1Q simply calculates the same day three months before. It would - however - be interesting to CALCDATE(’-1Q’, 31.5.01) as this should thoretically lead to 31st of february. ------- With best regards from Switzerland Marcus Fabian

As I understood, you want to know the enddate of the last quarter and the start date of current quarter ‘-1Q+CQ’ ‘-1Q+CQ+1D’ should be OK. Regards,

Quote: Same applies for -1Q which is defined as “minus three months”: Quote: In fact -1Q simply calculates the same day three months before. ------------------------ I am not sur about that! Hi Guys 365 and 366 are not divisable by four so! What is a Quarter? 13 weeks = 91 Days Not a real Quarter 3 months is Not a real Quarter So a Quarter is as near as we can get it Normal 91.25 Days ROUND(91.25,1) = 91 Days Leap Year 91.50 Days Leap Year ROUND(91.50,1)=92 Days Quote CQ: 06/30/2000 (correct) CQ-1Q: 03/30/2000 (incorrect – 03/31/2000 is the correct answer) ---------------- Year 2000 92 Days per quarter So we have April May and June April 30 Days May 31 days June 30 Days Take off the extra Day 92 Days should return 03/30/2000 Year 2001 91 Days per quarter So we have April May and June April 30 Days May 31 days June 30 Days 91 Days should return 03/31/2001 CalcDate seems to caculate the No of days then works out the date, ignoring the date table! Trendscapes Date Filters seems to work different it’s shows 3 (months) as a caption but a tool tip of a quarter and caclulates 3 Months, so the tool tip is wrong it should be 3 Months. Note: I use the PeriodFormManagement codeunit to return the date filter which i then use in my reports so they match the trendscapes. What happens if I want Accounting Quarters and my year starts 01 oct?? Edited by - David Cox on 2001 Jun 19 11:36:39

Topic moved from NF Developer Forum to NF Known Errors Best regards, Erik P. Ernst, webmaster Navision Online User Group

This is not a bug but just not uderstanding how the –1Q or even –1M works, see the example below. Date CalcDate Result 1/12/01 -1Q 1/9/01 30/12/01 -1Q 30/9/01 31/12/01 -1Q 30/9/01 28/5/00 -1Q 28/2/00 29/5/00 -1Q 29/2/00 30/5/00 -1Q 29/2/00 31/5/00 -1Q 29/2/00 31/5/01 -1Q 28/2/01 So Navision gets the same date three month before but if that is after the month end then it uses the month end date. So if you want the Quarter end date you have to use CQ+1D-1Q-1D. You have to do this so that the –1Q operates on the first of the month, simple really when you know whats going on. (the dates are in english format) Paul Baxter Edited by - triff on 2001 Jun 19 11:34:50

This topic has shown how little people know of the calcdate function. This includes me as I have learnt thing to day so here is a quick run through. -CQ = the first day of the current quarter +CQ = the Last day of the current quarter Same with CM and CY except for month and year. +1M one month after (so 2/2/00 +1M is 2/3/00) If this results in a date after the month end then the month end date is used ie 31/5/01 +1M is 30/6/01. So if you want to go through Months, Quarters or years make sure you use the 1st day of the period to navigate on or you will get odd results. So to find the date at the end of the month following the current quarter use CQ+1D+1M-1D. I hope this helps people Paul Baxter

quote:


Originally posted by triff: This topic has shown how little people know of the calcdate function.


The Issue here is not the understanding of the Calcdate function. Just that the Calcdate function returns differeent information to the DateFilters. Date Filters use the Date Table to return the Opening and Closing dates. If you run the table then you would see that Q1 for 2000 would be 01/01/2000 to 31/03/2000C. Q2 for 2000 would be 01/04/2000 to 30/06/2000C. So if the Calcdate function used this table the return would be Normaldate 31/03/2000 It seems that the CalcDate function does not. David Cox MindSource (UK) Limited Navision Solutions Partner Email: david@mindsource.co.uk Web: www.mindsource.co.uk

Hi Paul,

quote:


Originally posted by triff: So if you want the Quarter end date you have to use CQ+1D-1Q-1D. You have to do this so that the –1Q operates on the first of the month, simple really when you know whats going on. (the dates are in english


As you said, knowing what’s going on is the key :wink: Navision evaluates from left to right (so the order is important) and uses the newly created date for each shortcut as the reference date for the following, therefore you can create your calc formula exactly this way: instead of CQ+1D-1Q-1D you can use //go back 1Q, take this new date as the reference, go forward to the end of the quarter calcdate(’-1Q+CQ’, TODAY) which is nothing more than: d:=calcdate(’-1Q’, TODAY) // somewhere back in 1. quarter d:=calcdate(’+CQ’, d) // take the end of the quarter Torsten

David Yes and a calcdate of -Q1 will return a date of 1/1/01 Which is the start of quarter 1. -Q2 returns the 1/4/01, +Q1 return 1/1/02 which is the start of the next quarter1. So what is your problem just read the documentation that is working how you want it to work. What I am say that you and other does not understand is what happen with a –1Q that returns a date 3 months before to the day or the last day of the month so –1Q of 30/5/01 is 28/2/01. Then it follows that –1Q from 30/6/01 is 30/3/01 which it is, and not the 31/3/01 which you so desperately want it to be. The correct formula you want to us is ‘+1D-1Q-1D’ so you get +1D of 30/6/01 is 1/7/01 -1Q of 1/7/01 is 1/4/01 -1D of 1/4/01 is 31/3/01 Do you see what I am trying to say. No where in the documentation does it say that –1Q return the end of the previous Quarter just that it calculated 3 months before. It is not a bug just that people do not understand what is happening. Try this create a form with 3 text boxes and create 3 global variable 2 of date type and one of dateforumla type. The assign these variable to the textboxes and put this on the first date and the dateformular onvalidate trigger date2:=CALCDATE(dateform,date1); Change the dates and date formula and see what happens Paul Baxter

Torsten Your formula tested fine and for Months as well February. ‘-1M+CM’ Paul If I ask for the CQ to return the last day, then say -1Q would it not be reasonable to expect the last day of the previous Quarter?. David Cox MindSource (UK) Limited Navision Solutions Partner Email: david@mindsource.co.uk Web: www.mindsource.co.uk Edited by - David Cox on 2001 Jun 19 15:30:29

David This is the art of programing and why bugs occur because many time things seem reasonable but it is not how they work. Paul Baxter Edited by - triff on 2001 Jun 19 18:26:26

I think we all would be happier if Navision would calculate -1Q depending on whether or not the base-date is end of quarter or not in the case startdate := 17/6/01; calcdate(’-1Q’,startdate); it would be sufficient to calculate -3 months thus giving the result of 17/3/01 However if startdate is end of quarter: startdate := 30/6/01; calcdate(’-1Q’,startdate) it can be assumed that the user wants to have the real end of quarter returned. 31/3/01 and not 30/3/01. To get away from all this hazle I try to rely on save information. And the only thing which is save is the fact that every months/quarter starts with day ONE. That’s why I like the formula ‘CQ+1D-1Q-1D’ as it first points the date to a defined state (Current Quarter + 1 Day) which is always day number one. But calcdate(’-1Q+CQ’, TODAY) is even better as it is shorter. Before the year 1571 one year consisted of 12 moons with 30 days each. Very easy task for the programmers at this time (if there were any). But soon astronomers realized that they are missing some days as the midsummer day wouldn’t reflect the zodiac of the sun anymore. That’s why pope Gregor introduced the still valid Gregorian calendar in October 1571. 360 would be a number which somehow matches to nature why 365.25 is not. Esoterical Astrology explains this difference of 5 days as the “evil in the world”. These additional 5 days which make the sidreal year were creating a gap into the devine timeframe of 360 allowing evil to come to mankind. Such as Navisions CALCDATE function ------- With best regards from Switzerland Marcus Fabian

Yes Marcus I sort of agree. The best way to use Calcdate functions is to find the end of the previous quarter call -1Q+CQ. Also -1Q-CQ will find the start of the previous Quarter. These must be the correct way to do the calculations you are trying to do. As far as Navision using different methods of calculation with -1Q when it happens to be on the quarter end date I think would be asking for trouble. I am sure we would get another bug report in CALCDATE if the calculation were done that way. I say we have all learnt a bit more of how to use the Calcdate function correctly. We should also remember that all the discussions about quarters can be done with weeks, months and years as well. Paul Baxter