Other Rocket Products

 View Only

It's that time of year for DATE formulae to fail in CorVu

  • 1.  It's that time of year for DATE formulae to fail in CorVu

    Posted 01-04-2021 05:56
    Very often around this time of the new year CorVu queries and prompts fail to calculate dates correctly. They produce odd dates like 31/12/1899 00:00:00 or 01/01/1601 24:00:00 which is very unsettling.
    It is like the dreaded millennium bug every year - BUT in this case it is not a bug. It's the user definable Calendar function provided to give you the flexibility to define the business's fiscal calendar for each year. Some businesses use a 13 period calendar, it the UK we have the fiscal year-end in March, not December and others have specific month-ends which must fall on a weekend etc..etc. So this means the Calendar definitions file (called default.ca1) has to be part of the annual maintenance procedure for a CorBusiness (& HyperVu) installation.

    The date functions in CorVu usually use the Calendar function in order to work out fiscal periods, quarters, and first & last dates.
    The sudden occurrence of Invalid dates in previously healthy results are often the result of having an incomplete calendar file - usually this is the Default.ca1 file located in your CORVU program folder and/or the HyperVu servers folder.(although you can defined multiple custom calendar files - sooo much flexibility!)
    Edit that file with you favourite text editor and add the Month details for 2021 and 2022 (you always need at least 1 year after and 1 year before the date being formulated). And make sure when saving the file that the file extension remains as ca1, and all users have read permissions to it. Also, if you have a HyperVu installation, make sure to copy it to the servers folder so HyperVu users don't start calling you up.

    If you are using CorVu 5.40 the the following syntax shortcut is available. In the lines below it means that all years between 1988 and 2025 will use the Months= definition in the [default] section. Alternatively you will need to add a section for each year with its own Months= definition. The comments in the file provide some instruction.

    In the example below I have used January 1st as Period 1 in Q1 thru to December 1st as the start of period 12 in Q4. this will be used for ALL years between 1990 and 2025
    Of course someone will need to remember to check this again in 2025!
    Months = 1:1:1/1, 2:1:1/2, 3:1:1/3, 4:2:1/4, 5:2:1/5, 6:2:1/6, 7:3:1/7, 8:3:1/8, 9:3:1/9, 10:4:1/10, 11:4:1/11, 12:4:1/12

    With great flexibility comes great responsibility! - sorry, bit too much Netflix over the holidays.