Comparing Group Subtotals 1

[BryanLane]

I have a report that calculates the average days outstanding for each invoice. My report is grouped by customer ID and then by Due Date with the option set to "The section will be printed for each year."

Now I want to compare from year to year (the total for the current year group vs the previous year group) to see if the average days are getting better (smaller number) or worse (larger number).

I know how to use Previous for comparing records....how do you do it with groups?

"The problems we face today cannot be solved by the minds that created them."
--Albert Einstein

 

[lbass]

Depending upon how you are calculating average days, you might be able to simply insert a crosstab in the report footer with date (on change of year) as your column, and average as your summary. You could also insert a chart in the report footer that reports the average on change of year.

You could compare previous group values with the current group value, but I'm not sure that would meet your needs as well as a crosstab or chart.

-LB

 

[BryanLane]

Current to previous group comparison is what I am after. I will be presenting multiple years of data, so if I have something like:

GF3 2005 19
GF3 2006 25 Worse
GF3 2007 26
GF3 2008 15 Better
GF3 2009 40 Worse

I intended to use a parameter for the # of days difference to trigger the better or worse decision so that small changes don't show up, initially set at +/- 5 days so that a changes from 15 to 19 or 13 to 10 will not be flagged.

So what I am after is being able to compare the GF3 2006 value of 25 to the GF3 2005 value of 19 and come up with a change of +6 and the same thing for each succesive group (year).

"The problems we face today cannot be solved by the minds that created them."
--Albert Einstein

 

[lbass]

Okay, then create a formula like this to be placed in GF3:

whileprintingrecords;
numbervar prevave := ave;
numbervar ave := average({table.days},{table.group3field});
if not onfirstrecord and
{table.group2field} = previous({table.group2field}) and
ave - prevave >= 5 then "Worse" else
if ave - prevave <= -5 then "Better"

-LB

 

[lbass]

Sorry, that should be:

whileprintingrecords;
numbervar ave;
numbervar prevave := ave;
ave := average({table.days},{table.group3field});
if not onfirstrecord and
{table.group2field} = previous({table.group2field}) and
ave - prevave >= 5 then "Worse" else
if ave - prevave <= -5 then "Better"

-LB

 

[BryanLane]

I copied and pasted the second corrected one in to my report as a formula. It works for all the "subsequent" years (2nd, 3rd, 4th, etc) but the first year line for each Customer is comparing to the last year line from the prevous customer.

My formula looks like this:

whileprintingrecords;
numbervar ave;
numbervar prevave := ave;
ave := average({@Days O/s},{@Due Date}, "annually");
if not onfirstrecord and
{AROHST.CUSNO}= previous({AROHST.CUSNO}) and
ave - prevave >= 5 then "Worse" else
if ave - prevave <= -5 then "Better"

"The problems we face today cannot be solved by the minds that created them."
--Albert Einstein

 

[lbass]

I think that

if not onfirstrecord and
{AROHST.CUSNO}= previous({AROHST.CUSNO}) and

should have addressed this. What value is the formula returning for the first customer row? Can you show a sample that illustrates the issue?

-LB

 

[BryanLane]

I also thought the "not onfirstrecord" would suppress the calculation for the first row for each customer.

The first year row for the first customer in the report returns a 0 (zero) average days outstanding and a blank for the Better/Worse calculation.

The last row for the first customer has an average days outstanding of (2) and the first year for second customer has an average days outstanding of 10. It has a Better/Worse value of "Worse"

It continues like that down the report. If the value for the first row of a customer is within +/- 5 of the last row of the previous customer it has nothing showing in the Better/Worse field.

Here is a listing of the first six customers for the first sales territory:

Cust# # of Inv Avg Days Better/Worse
GH1 020 House Wholesale
GH2 01147
GF3 2006 19 0
GF3 2007 12 0
GF3 2008 12 0
GF3 2009 3 (2)

GF2 Total 46 0

GH2 01204
GF3 2006 28 10 Worse
GF3 2007 25 6
GF3 2008 29 2
GF3 2009 8 4

GF2 Total 90 6

GH2 01533
GF3 2006 2 17 Worse
GF3 2007 1 50 Worse
GF3 2008 1 12 Better

GF2 Total 4 24

GH2 01550
GF3 2006 36 1 Better
GF3 2007 29 (3)
GF3 2008 28 2
GF3 2009 11 1
GF2 Total 104 0

GH2 10765
GF3 2006 15 4
GF3 2007 3 3

GF2 Total 18 4

"The problems we face today cannot be solved by the minds that created them."
--Albert Einstein

 

[lbass]

Are you suppressing any sections?

You could try changing the formula to:

whileprintingrecords;
numbervar ave;
numbervar prevave := ave;
ave := average({@Days O/s},{@Due Date}, "annually");
if not onfirstrecord and
{AROHST.CUSNO}= previous({AROHST.CUSNO}) then (
if ave - prevave >= 5 then "Worse" else
if ave - prevave <= -5 then "Better"
)

You could also create a reset formula to place in the GH#2:

whileprintingrecords;
numbervar ave;
if not inrepeatedgroupheader then (
ave := 0;
preave := 0
);

-LB

 

[BryanLane]

My initial response to your first question "Are you suppressing any sections?" was going to be no, because I am not suppressing any sections above section 3 (meaning 1 or 2), but then I realized I was supressing section 4 (APLNO "applied to number" which is the invoice number) and the detail records.

Could the hiding of Group 4 be affecting this? I tried unhiding it (which dramatically increases the number of pages) but it did not change the results of the Better/Worse value the first group of each customer.

I tried changing the formula for Better/Worse as shown, it resulted in a blank record for every GF3 in the entire report, not just the first one for each customer.

The GH2 formula resulted in an error stating a missing ")" and it highlighted the preave item on the third line. I noticed that there was a variable declaration for ave, but not for preave, so I tried adding a "numbervar preave;". this resulted in no error when saving the formula. I placed in in GH2, which resulted in a 0.00 value for each GH2, but it did not change the Better/Worse values.

I am starting to think I just need 355 pages of "Whiteout"



"The problems we face today cannot be solved by the minds that created them."
--Albert Einstein

 

[lbass]

Sorry, should have been:

whileprintingrecords;
numbervar ave;
numbervar preave;
if not inrepeatedgroupheader then (
ave := 0;
preave := 0
);

Please show the actual formula you are using and where you are placing it and identify the names of each group.

-LB

 

[BryanLane]

Here are the two formulas from the report:

In GH2 (based on AROHST.cusno) is:
whileprintingrecords;
numbervar ave;
numbervar preave;
if not inrepeatedgroupheader then (
ave := 0;
preave := 0
);


In GF3 (based on Due Date summarized "annually" is the formula:

whileprintingrecords;
numbervar ave;
numbervar prevave := ave;
ave := average({@Days O/s},{@Due Date}, "annually");
if not onfirstrecord and
{AROHST.CUSNO}= previous({AROHST.CUSNO}) and
ave - prevave >= 5 then "Worse" else
if ave - prevave <= -5 then "Better"



"The problems we face today cannot be solved by the minds that created them."
--Albert Einstein

 

[lbass]

This should be better, but you say it returned no values? Please try it again.

whileprintingrecords;
numbervar ave;
numbervar prevave := ave;
ave := average({@Days O/s},{@Due Date}, "annually");
if not onfirstrecord and
{AROHST.CUSNO}= previous({AROHST.CUSNO}) then (
if ave - prevave >= 5 then "Worse" else
if ave - prevave <= -5 then "Better"
)

I don't think your Group #4 suppression should matter as long as your averages appear to be correct. If you add a formula to the group #3 section:

average({@Days O/s},{@Due Date}, "annually")

...are the correct averages returned?

-LB

 

[BryanLane]

I created a new formula for the first one placed it in GF3 next to my existing Better/Worse formula, it still returns no value for any group. I check all 300+ pages.

I created a new formula for

average({@Days O/s},{@Due Date}, "annually")

and placed it next to my existing formula for avg days outstanding by year and it matches exactly. So yes, the correct averages are being returned.

"The problems we face today cannot be solved by the minds that created them."
--Albert Einstein

 

[lbass]

Okay, I think the problem is that the formula should be placed in GH#3, NOT in a group footer #3 section. Try displaying your group footer fields in GH#3 and reposition the formula to see if that returns the correct results.

-LB

 

[BryanLane]

That did the trick. I moved everything from GF3 to GH3 and hid GF3. Now it works perfectly.

I have learned so much about Crystal from following the help you have provided to everyone on this site.

Thank you very much for all your help and patience.

"The problems we face today cannot be solved by the minds that created them."
--Albert Einstein

 

[lbass]

I'm sorry I didn't pick up on the formula placement issue sooner.

-LB