0

Help with Formula for Fiscal Calendar Crystal Report

Dec 07, 2016 at 05:50 PM

47

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

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
Former Member Dec 12, 2016 at 11:05 AM
0

Hi Brian,

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

Share