Group Summaries - Can you subtract them?

[aj3221]

My goal is to create a lost business report. The data I am using is by customer, by year, by month, by product ($'s and Qty).

My groups are: customer, then product then year. My file has shipto level data so it gives me a line per item per shipto. I've added a summary that adds up each item by month for each year. I am comparing 2011 to 2010.

So now I have (for example):
CustNo: 7223
ItemNo: CAS1751
Year: 11 JanQty: 2 Jan$: $50 FebQty: 1 Feb$: $25.00
Year: 10 JanQty: 0 Jan$: $0 FebQty: 7 Feb$: $175.00

Because the totals by month are summaries, I can't figure out how to subtract 2011 from 2010.

I hope that makes sense.

Thanks in advance for any assistance!

 

[lbass]

Yes, you can subtract them. Please show the content of your monthly qty and amount formulas for January.

-LB

 

[aj3221]

JanQty formula:
tonumber (split({smsew.qtysold},";")[1])

Jan$ formula:
tonumber (split({smsew.salesamt},";")[1])

Then it's a sum of @JanQty and a sum of @Jan$ for each year.

My screen looks like:

Group#1 (CustNo)
Group#2 (ItemNo)
Group#3 (Year) Sum of @JanQty Sum of @Jan$ Sum of @FebQty Sum of

 

[lbass]

Create formulas like this:

//{@accum} to be placed in the year group section:
whileprintingrecords;
numbervar qty1_11;
numbervar qty2_11;
numbervar amt1_11;
numbervar amt2_11;
numbervar qty1_10;
numbervar qty2_10;
numbervar amt1_10;
numbervar amt2_10;
if {table.year} = 2011 then (
qty1_11 := sum({@JanQty},{table.year});
qty2_11 := sum({@FebQty},{table.year});
amt1_11 := sum({@Jan$},{table.year});
amt2_11 := sum({@Feb$},{table.year})
);
if {table.year} = 2010 then (
qty1_10 := sum({@JanQty},{table.year});
qty2_10 := sum({@FebQty},{table.year});
amt1_10 := sum({@Jan$},{table.year});
amt2_10 := sum({@Feb$},{table.year})
);

Then display the results in the Item Group footer by creating separate formulas per month, like this:

//{@Janqtydiff}:
whileprintingrecords;
numbervar qty1_11;
numbervar qty1_10;
qty1_11-qty1_10

-LB