Rolling 12 month period sales over previous 12 month period sales

[leftfldr5]

I need to create a report where customer sales increased x$ for this 12 month period over the last 12 month period. Parameters are period, year, and increase. If the user enters parameters of 12, 2005, and 5000.00 then they can see the customers whose sales for period 1 - 12 of 2005 increased by 5000.00 over the sales for periods 1 - 12 of 2004. We are running under Crystal 10 with a SQL database.

Setup

Table has the following fields:
Year, Period, CustID, Sales $

The data looks like this:
2006, 1, ABC, 200.00
2006, 2, ABC, 400.00
2006, 3, ABC, 100.00
2005, 1, ABC, 150.00
2005, 2, ABC, 200.00

There is a separate record for each period and year combination per customer.

If User enters: Period 10 Year 2005
The report needs to be on a rolling 12 months so I would want Period 1 - 10 of 2005 and Period 11 and 12 of 2004 for the current sales and Period 1 - 10 of 2004 and Period 11 and 12 of 2003 for last year sales

Report output:
Cust# Sales $ Current Sales & Last
ABC $$$ $$$$
BBB $$$ $$$$



PROGRESS SO FAR:
I have read the rolling threads, but most of them have to do with one record per Year and I have muliple records per year. Not sure if thread767-991283 would help me.

PROBLEM:
Do I use the select expert or formulas to accomplish this or a combination?

Suggestions, comments, follow up questions all will help.

THANKS!

 

[lbass]

I know you are trying to be helpful, but this sample doesn't really work. The results you are seeing are not random, and for us to detect what's wrong, we have to be able to see how the formulas are behaving. Please break down the accum formula into two different formulas, and place them in the GF2 sections of your sample above, and then report back. This way we'll be able to watch them as they work. Also, make sure that the two groups you are using are consecutive in your report, so we can tell for sure that the reset is working correctly. Do not suppress any GF sections.

The formulas should look like this:

//@accumselyr}:
whileprintingrecords;
currencyvar selyr;

if sum({@difference},{RM00101.CUSTNMBR}) >= {?Sales Increase or (Decrease)} then
selyr := selyr + {@Selected Period/Year};

//@accumprevyr}:
whileprintingrecords;
currencyvar prevyr;

if sum({@difference},{RM00101.CUSTNMBR}) >= {?Sales Increase or (Decrease)} then
prevyr := prevyr + {@Previous Period/Year};

You don't have to change the other formulas. Also please identify what your parameter value is if it is different thatn 5000.

This shouldn't be that difficult, so my guess is that there is some small thing that is interfering with this working properly.

-LB

 

[leftfldr5]

Problem solved! I moved the formula for group supression: sum({@Difference},{table.custID}) < {?SalesIncr} to the group select expert and changed the logic so now my running totals will work correctly since I am limiting the data upfront instead of suppressing it. Thanks for all the help - sure is appreciated.