Skip to Content
avatar image
Former Member

Help with Formula for Fiscal Calendar Crystal Report

Hi Experts,

I have been asked to write a monthly and yearly report based upon a fiscal Calendar. The code below is what we have been given and I would ask assistance to help transform this code into a Crystal Formula's

Any suggestions would be appreciated.

if (Sysdate() -2 >= ToDate( 20160103) && Sysdate() -2 < ToDate(20160131)) {1 } else { if (Sysdate() -2 >= ToDate( 20160131) && Sysdate() -2 < ToDate(20160228)) { 2 } else { if (Sysdate() -2 >= ToDate( 20160228) && Sysdate() -2 < ToDate(20160403)) { 3 } else { if (Sysdate() -2 >= ToDate( 20160403) && Sysdate() -2 < ToDate(20160501)) { 4 } else { if (Sysdate() -2 >= ToDate( 20160501) && Sysdate() -2 < ToDate(20160529)) { 5 } else { if (Sysdate() -2 >= ToDate( 20160529) && Sysdate() -2 < ToDate(20160703)) { 6 } else { if (Sysdate() -2 >= ToDate(20160703) && Sysdate() -2 < ToDate(20160731)) { 7 } else { if (Sysdate() -2 >= ToDate(20160731) && Sysdate() -2 < ToDate(20160828)) { 8 } else { if (Sysdate() -2 >= ToDate(20160828) && Sysdate() -2 < ToDate(20161002)) { 9 } else { if (Sysdate() -2 >= ToDate( 20161002) && Sysdate() -2 < ToDate(20161030)) { 10 } else { if (Sysdate() -2 >= ToDate( 20161030) && Sysdate() -2 < ToDate(20161127)) { 11 } else { if (Sysdate() -2 >= ToDate( 20161127) && Sysdate() -2 < ToDate(20170101)) { 12 } } } }}}}}}}}}

Regards,

Fraser

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Dec 07, 2016 at 06:18 PM

    Hi Fraser,

    Have a look at this formula. I prefer to use Case statements instead of nested Ifs because I find it easier to follow.

    Select (CurrentDate - 2) 
        Case Date (2016, 01, 03) To Date (2016, 01, 30): 1 
        Case Date (2016, 01, 31) To Date (2016, 02, 27): 2 
        Case Date (2016, 02, 28) To Date (2016, 04, 02): 3 
        Case Date (2016, 04, 03) To Date (2016, 04, 30): 4
        Case Date (2016, 05, 01) To Date (2016, 05, 28): 5  
        Case Date (2016, 05, 29) To Date (2016, 07, 02): 6 
        Case Date (2016, 07, 03) To Date (2016, 07, 30): 7 
        Case Date (2016, 07, 31) To Date (2016, 08, 27): 8 
        Case Date (2016, 08, 28) To Date (2016, 10, 01): 9 
        Case Date (2016, 10, 02) To Date (2016, 10, 29): 10 
        Case Date (2016, 10, 30) To Date (2016, 11, 26): 11 
        Case Date (2016, 11, 27) To Date (2016, 12, 31): 12 
        Default: 0; 
    

    So we use Crystal's CurrentDate instead of SysDate().

    Each case has your ranges. I ran this and I got 12 which is what I would expect for today.

    Give it a shot and see if it works for you.

    Brian

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Dec 12, 2016 at 11:05 AM

    Hi Brian,

    Many thanks for your help. I will give this a try.

    Add comment
    10|10000 characters needed characters exceeded