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!

 

[leftfldr5]

Bump.....Anyone??? I could really use some help on this one.

 

[lbass]

I think you could use a record selection formula like:

(
{table.period} <= {?Period} and
{table.year} in {?Year}-1 to {?Year}
) or
(
{table.period} > {?Period} and
{table.year} in {?Year)-2 to {?Year}-1
)

Then create three formulas like the following:

//{@Selected Period/Year}:
if
(
{table.period} <= {?Period} and
{table.year} = {?Year}
) or
(
{table.period} > {?Period} and
{table.year} = {?Year}-1
) then
{table.sales}

//{@Previous Period/Year}:
if
(
{table.period} <= {?Period} and
{table.year} = {?Year}-1
) or
(
{table.period} > {?Period} and
{table.year} = {?Year}-2) then
{table.sales}

//{Difference}:
{@Selected Period/Year}-{@Previous Period/Year}

Insert a group on {table.CustID} and then add the above formulas to the detail section. Right click on each and insert a summary (sum) at the group level. Drag the group name into the group footer and then suppress the group header and details section.

Then go to the section expert->group footer->suppress->x+2 and enter:

sum({@Difference},{table.custID}) < {?SalesIncr}

-LB

 

[leftfldr5]

Thanks for the help - REAL CLOSE!
Here is a sample of the months/years I get if the I enter Year = 2005 and Month = 10

**Everything looks good except notice that I get 12 of 2004 twice which doubles the sales for that period/year.

1 2,004
2 2,004
3 2,004
4 2,004
5 2,004
6 2,004
7 2,004
8 2,004
9 2,004
10 2,004
11 2,004
12 2,004
11 2,003
12 2,003
12 2,004
1 2,005
2 2,005
3 2,005
4 2,005
5 2,005
6 2,005
7 2,005
8 2,005
9 2,005
10 2,005

How can I elimintate this duplicate?
Thanks!

 

[lbass]

Please post your exact record selection formula.

-LB

 

[leftfldr5]

HISTYPE 0 being calendar year (HISTYPE 1 is fiscal year so it would duplicate)

{RM00104.HISTTYPE} = 0 and

(
{RM00104.PERIODID} <= {?Month} and
{RM00104.YEAR1} in {?Year}-1 to {?Year}
) or
(
{RM00104.PERIODID} > {?Month} and
{RM00104.YEAR1} in {?Year} - 2 to {?Year} - 1
)

 

[leftfldr5]

Does not happen if you enter 12 and 2005.

 

[lbass]

Try:

{RM00104.HISTTYPE} = 0 and
(
(
{RM00104.PERIODID} <= {?Month} and
{RM00104.YEAR1} in {?Year}-1 to {?Year}
) or
(
{RM00104.PERIODID} > {?Month} and
{RM00104.YEAR1} in {?Year} - 2 to {?Year} - 1
)
)

-LB

 

[leftfldr5]

Awesome! I appreciate the help - it works great.

 

[leftfldr5]

I have this report grouped by Class ID then Customer ID. Is there any way to get group(Class ID) totals for {@Previous Period/Year} and {@Selected Period/Year}formulas since I am suppressing records in the Cust ID group? I have tried a running total, but still get totals including the suppressed records.

 

[lbass]

Please explain why you need to suppress records--could these extra records be eliminated by record selection?

If you have row inflation and need to use running totals, then you should explain how you have the running totals set up. There is probably a way to account for the suppressed records in the running totals. You need to also provide the formula you are using for suppression.

-LB

 

[leftfldr5]

From your first response you had me do a suppress formula on the group footer that is:

sum({@Difference},{table.custID}) < {?SalesIncr}

to suppress the records that do not meet the increase parameter.

It works perfectly, but I do need group totals for the formula fields.

 

[lbass]

Sorry, forgot about that. Use variables to sum the results at the group level. Create three formulas:

//{@reset} for the ClassID group header:
whileprintingrecords;
numbervar selyr := 0;
numbervar prevyr := 0;


//{@accum} for the CustID group header or footer:
whileprintingrecords;
numbervar selyr := selyr + {@Selected Period/Year};
numbervar prevyr := prevyr + {@Previous Period/Year};

//{@displayselyr} for the Class ID group footer:
whileprintingrecords;
numbervar selyr;

//{@displayprevyr} for the Class ID group footer:
whileprintingrecords;
numbervar prevyr;

if {table.sales} is a currency, change all the variables in the above formulas to "currencyvar" instead of "numbervar".

-LB

 

[leftfldr5]

Ok the reset seems to be working fine and resetting to 0.00 at change of Class ID.

However, I could not get @accum to give me correct numbers either in CustID group header or footer. I get closer if I use:
whileprintingrecords;
currencyvar selyr := Sum ({@Selected Period/Year}, {RM00101.CUSTNMBR});

BUT as soon as I change it to:
whileprintingrecords;
currencyvar selyr := selyr + Sum ({@Selected Period/Year}, {RM00101.CUSTNMBR});
the selyr adds in the suppressed records

 

[lbass]

Sorry, I realized after I left the office that I had made a mistake...

Change {@accum} to:

//{@accum} for the CustID group header or footer:
whileprintingrecords;
currencyvar selyr;
currencyvar prevyr;
if sum({@difference},{RM00101.CUSTNMBR}) >= {?SalesIncr} then
(
selyr := selyr + {@Selected Period/Year};
prevyr := prevyr + {@Previous Period/Year}
);

-LB

 

[leftfldr5]

No problem. I am still struggling with this and have tried a million combinations. The reset is still working but the @accum does not accumulate right.


GH1 - EQUIPMENTDEALER 0.00 (GH reset)
@accum cust name sel yr prev yr diff
$0.00 10337 AVIA SOURCE IN 19,314 3,364 15,950
$0.00 11239 KIT COMPONENTS 28,219 21,821 6,398 $10,457.50 11490 LABINAL 209,672 81,751 127,921
See how the @accum does not match the sel yr.

THANKS!

 

[lbass]

The accum formula should be suppressed, as it will only show the prev year as it is being accumulated. It is the group footer formula that will display the correct values. Please copy your formula for {@accum} into the thread and identify the parameter value you used for the sample above. Are you showing all records or only non-suppressed records? Also make sure all formulas (reset and display, too) are using the same variable names and currencyvar instead of numbervar.

-LB

 

[leftfldr5]

Parameters : Month-2, Year-2006, Increase-5000
Only showing non suppressed records
All formulas are using currencyvar and same variable names

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

 

[lbass]

Need more information. Please provide a sample like the one above where you show the values for the group footer (classID) formulas and also where the group footer (custID) section is not suppressed. Please include at least two classID instances so I can see what it is happening with the formulas. Also show the {@accum} in the custID group footer, so I can see how it is accumulating at least for the previous year.

-LB

 

[leftfldr5]

@accum for the GF2 for all these records is 0.00 but the selected year and prev year formulas have a total in the GF1.

GH1 END CUSTOMER 0.00 (reset)
cust sel yr prev diff
GF2 10180 5770 0 5770
12045 6528 735 5794
12559 6204 0 6204
12606 8444 0 8444
12665 7630 0 7630
21566 6570 0 6570
26017 7358 0 7358
GF1 TOTAL 10625 0


GH1 DISTRIBUTOR 0.00 (reset)

GF2 11705 78483 63705 14778
12111 162511 133028 29483
18744 116149 93969 22179
20300 34770 18189 16581
2400 47371 36374 10997
7478 371781 85528 289253
8301 122781 101873 20908
8559 521889 512903 8987
GF1 TOTAL 94853 0