Rolling Six Months Data by Current Month

[Former Member]

Dear Legends,

I am trying to create a group on rolling 6 months data to show up sales for whole year by months.I manage to write last six months but it doesnt roll up any further.

IF {@TRANDATE} IN DATEADD("m",-6,CURRENTDATE) - DAY(DATEADD("M",-6,CURRENTDATE))+1 TO

DATEADD("M",-0,CURRENTDATE) - DAY(DATEADD("M",-0,CURRENTDATE))

THEN TOTEXT(DATEADD("M", 0, CURRENTDATE), "MMM-yyyy")

ELSE IF {@TRANDATE} IN DATEADD("M",-7,CURRENTDATE) - DAY(DATEADD("M",-7,CURRENTDATE))+1 TO 

DATEADD("M",-1,CURRENTDATE) - DAY(DATEADD("M",-1,CURRENTDATE))

THEN TOTEXT(DATEADD("M", -1, CURRENTDATE), "MMM-yyyy")

ELSE IF {@TRANDATE} IN DATEADD("m",-8,CURRENTDATE) - DAY(DATEADD("m",-8,CURRENTDATE))+1 TO

DATEADD("M",-2,CURRENTDATE) - DAY(DATEADD("M",-2,CURRENTDATE))

THEN TOTEXT(DATEADD("M", -2, CURRENTDATE), "MMM-yyyy")

ELSE TOTEXT("Jan-2070")

It display Nov-2014 (01-05-2014  to 31-10-2014) Which is exactly what do I want.

But it moves Oct-2014 it only shows data for month of (Apr-2014). What do I need is Apr-2014 to Sep-2014.

I shall be grateful if someone could hep me out.

Kind Regards,

Aamir

[former_member292966 · Accepted solution]

Hi Aamir,

If I understand what you are trying to do, you want your records grouped like:

Nov-2014

   May-1024

   Jun-2014

   Jul-2014

   Aug-2014

   Sep-2014

   Oct-2014

Oct-2014

   Apr-2014

   May-2014

   Jun-2014

   Jul-2014

   Aug-2014

  Sep-2014

Sep-2014

   Mar-2014

   Apr-2014

   May-2014

   Jun-2014

   Jul-2014

   Aug-2014

You can't do this in a single formula because once Crystal has processed a record, it can't go back and group it again.  So when you move from November to October groups, the only records that weren't grouped were from April which is why you don't see the May - September records.

I recommend creating a group on the @TRANDATE formula and set the grouping per month.  Hide the Detail section.

In the group, insert a subreport that returns the records for the previous 6 months for that group.  The subreport will run for each month which allows it to always get the records in the 6 month period.

Hope this makes sense.

Brian

[Former Member · Accepted solution]

Hi Brian,

Thanks for your reply. I tried write separate formulae and modified a little.

NumberVar ADDAMONTH := 1;

If datepart("yyyy", {@date}) = {?PYEAR} and {@date} in DateAdd ("m",-({?TO-MONTH}+ADDAMONTH),CurrentDate) -

Day (DateAdd("m",-({?TO-MONTH}+ADDAMONTH),CurrentDate))+1 to

DateAdd ("m",-({?FROM-MONTH}+ADDAMONTH),CurrentDate) -

Day (DateAdd("m",-({?FROM-MONTH}+ADDAMONTH),CurrentDate)) then {QTY}

Thank you so much once again for sharing your knowledge.

Kind Regards

Aamir