Skip to Content
0

Help with Formula for Fiscal Calendar Crystal Report

Dec 07, 2016 at 05:50 PM

47

avatar image
Former Member

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

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Brian Dong Dec 07, 2016 at 06:18 PM
0

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

Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member Dec 12, 2016 at 11:05 AM
0

Hi Brian,

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

Share
10 |10000 characters needed characters left characters exceeded