Calculation in group footer to subtract fields in different groups

[tomed]

Ok - let's see if I can explain this:

I have a report that finds the total number of each item sold to each customer by month. It shows sales data from this year along with the previous. I've got the totals working correctly. What I need to do now is take the amount of items sold this year in January (for example) minus the amount sold last year at the same time, and show a + or - number.

I have three groups in the report: Item #, Year, Cust ID. The January total for all customers (the number I'm trying to compare) is in the Year footer. I need to know if there is a way to subtract the January 2001 total from the January 2002 total inside the footer.

I've been working on this report for a few days and going about it all different ways and I keep running into this. Any help would be GREATLY appreciated.

Thomas

 

[Ngolem]

need help in visualizing your report


is it :

Item#
Year
Customer

or

Customer
Item#
year

You say this is for the year...is this like a fiscal year end? You show a monthly total in your example.

Basically the solution to your problem is to store the first total in a variable to be subtracted later when the second total is subtracted....there are issues if there are more than 2 years of data but they are easy to solve once the structure is known

Show us a sample basic printout Jim Broadbent

 

[tomed]

Thanks for the help Jim. I'm using a variable to store the first total now. It seems to be working fine when I subtract it from the second. In case you were still curious, the grouping is

Item#
Year
Customer

The Customer footer looks like this:

CustomerName jan_tot feb_tot march_tot

And the year footer like this:

jan_tot(all cust) feb_tot(all cust)
prev_tot-current(jan) prev_tot-current(feb)
...and so on

Thanks again!

 

[Ngolem]

ok...the picture is clearer...but needs tweaking

so with each year you have 12 months x customers worth of records



so you have a set of formulas (wish you would show them) to reset values...I am ASSUMING you only are getting 2 years of data....to do this you would need a STARTYEAR and ENDYEAR parameter to limit your data...AGAIN AN ASSUMPTION

then your formulas should be something like this

in the Group1 header (suppressed)

@Initialization

WhilePrintingRecords;
//the thirteenth is the total for the year
if not inRepeatedGroupHeader then
(
NumberVar Array Year1_Tot := [0,0,0,0,0,0,0,0,0,0,0,0,0];
NumberVar Array Year2_Tot := [0,0,0,0,0,0,0,0,0,0,0,0,0];
);



Now in the detail section you have a calculation formula
Now I am going to ASSUME you have a field called month which is numeric corresponding to JAN, FEB etc.

@Calc (suppressed in detail section)

WhilePrintingRecords;
NumberVar Array Year1_Tot;
NumberVar Array Year2_Tot;

if {Table.year} = {?StartYear} then
(
Year1_Tot[{Table.month}] := Year1_Tot[{Table.month}] +
{Table.number_to_be_summed};
//yearly total
Year1_Tot[13] := Year1_Tot[13] +
{Table.number_to_be_summed};
)
else
(
Year1_Tot[{Table.month}] := Year1_Tot[{Table.month}] +
{Table.number_to_be_summed};
//yearly total
Year1_Tot[13] := Year1_Tot[13] +
{Table.number_to_be_summed};
);


now you compose your display formulas..I will only do January but the same format is used for rest...including the Year total

@Display_January (Displayed in the Year Footer)

WhilePrintingRecords;
NumberVar Array Year1_Tot;
NumberVar Array Year2_Tot;

if {Table.year} = {?StartYear} then
Year1_Tot[1]
else
Year2_Tot[1];

Now to display the difference between the two rows it is done with formulas like the following:

@Display_January_comparison (Displayed in the Item Footer)

WhilePrintingRecords;
NumberVar Array Year1_Tot;
NumberVar Array Year2_Tot;

Year1_Tot[1] - Year2_Tot[1];


that should work fine...really what you are making is a manual cross tab...I like this as I fine the Crystal cross tab too restrictive. Jim Broadbent