4 Replies Latest reply: Jan 6, 2010 4:37 PM by Wayne Regnier RSS

Fiscal year formula

Wayne Regnier
Currently Being Moderated

Crystal 11, I understand grouping dates via traditional calendar ranges but how do you create a formula that groups 10 years of monthly data into our Fiscal year grouping which is September 1 to August 31?

 

Thanks

  • Re: Fiscal year formula
    sharon towler
    Currently Being Moderated

    You can create 2 formulas that extracts the month and year from the date field. 

     

    month( {SuperCheck.CkDate})

     

    (year({SuperCheck.CkDate})-10.00) -- this will return your year 10 years prior

     

    create a group formula

     

    if datefield in month and year then true

     

    something like that will work.

     

    you may want to create parameters to feed the dates to run at any time.

    • Re: Fiscal year formula
      Wayne Regnier
      Currently Being Moderated

      Will this group each fiscal year seperately?  so the months in each fiscal group will be september 1999 to august 2000 and the next group will be for months september 2000 to august 2001 and so on??

      • Re: Fiscal year formula
        sharon towler
        Currently Being Moderated

        if you create a year(field) formula

        than create a group from that iit should group by the year.

         

        you will need to try it, the data im working with i cant test it for you

        • Re: Fiscal year formula
          Wayne Regnier
          Currently Being Moderated

          Sorry for being so elementary here.

           

          I have this date field {Electrical_.Begin_Date} that actually goes back to 1997.

           

          Created one formula called Month:  Month({Electrical_.Begin_Date})

           

          Created another formula called Year: (year({Electrical_.Begin_Date})-13.00)

           

          I went to design view and change group and check marked use Formula as Group Sort Order and

          created Group Formula: (year({Electrical_.Begin_Date})-13.00) and receive an error "A Constant Expression is required here" with the Electrical_Begin_Date highlighted.

           

          My report group is on the Electrical_Begin_Date field.

           

          Tried to create another Group Formula: if {Electrical_.Begin_Date} in {@Month} and {@Year} then true

          and receive an error "A Date-Time is required here with the @Month highlighted.

Actions