Difference between sum of a group 2

[kinosaidp]

Hello!

I'd like to get the difference between summaries of a group something like this;

group1_total = 100 <--- 1st summary
group1_total = 200 <--- 2nd summary

Using the 2 summaries of the above group I'd like to display my output in the report footer as
Net = 100 which is a result of 2nd summary - 1st summary.
I'm using Crystal 11 and SQL 2000.

Thanks.

 

[Zbrojovka]

LBass, in the beginning of this post you helped another user by suggesting the formula below. My scenario appears to be exactly as his was. I was having trouble determining what value belonged in "Instance1" and "Instance2".

Is this off track from what I need?

whileprintingrecords;
numbervar A;
numbervar B;

if {table.group} = "Instance1" then
A := sum({table.amt},{table.group};
if {table.group} = "Instance2" then
B := sum({table.amt},{table.group};

Then in the report footer use this:
whileprintingrecords;
numbervar A;
numbervar B;
B - A

 

[lbass]

Please show me the formulas you used!!! My solution would have returned 4, so you have implemented it incorrectly. If you won't show the actual formulas, I cannot help.

-LB

 

[Zbrojovka]

Here's yours:

whileprintingrecords;
numbervar curr;
numbervar prev := curr;
curr := distinctcount ({inmain.inci_id},{inmain.calltime},"Monthly");
if onfirstrecord or
{inmain.nature} <> previous({inmain.nature}) then
curr else 0; //not sure whether you want this to be 0 or current value
curr - prev

 

[lbass]

Okay, I know what the problem is. My formula is set up to show a running difference between the groups--and it is intented to be placed in the group #2 section, not in the Group #1 section. You would need to do it this way if there could be more than two months in a group #1. So please clarify if there are ever more than two months.

Also, if you want the diff to be 0 on the first group #2 instances, then your formula should be:

whileprintingrecords;
numbervar curr;
numbervar prev := curr;
curr := distinctcount ({inmain.inci_id},{inmain.calltime},"Monthly");
if onfirstrecord or
{inmain.nature} <> previous({inmain.nature}) then
[red]0[/red] else
curr - prev

-LB

 

[Zbrojovka]

Thank you for the ending correction.

As for more than two months? The potential is there for it sometime in the future. But for simplicity I would accept a solution that will just do the 1 vs 1 comparison and tackle the other later.

 

[lbass]

Okay, then you need two formulas:

//{@accum} for the Group #2 section:
whileprintingrecords;
numbervar curr;
numbervar prev := curr;
curr := distinctcount ({inmain.inci_id},{inmain.calltime},"Monthly");

//{@displdiff} for the Group #1 footer:
whileprintingrecords;
numbervar curr;
numbervar prev;
curr-prev

-LB

 

[Zbrojovka]

Okay, this is 90% on!!! It's looking really good. However, and you are going to want to strangle me I'm sure, an anomaly has been presented.

There will always be a comparison between two months, but sometimes for a given nature there was no reported event in the date range specified. So, I end up with a value for say just January. This creates an incorrect comparison on the count. Ideas? Otherwise this is exactly what I need.

 

[lbass]

Okay, change this formula:

//{@displdiff} for the Group #1 footer:
whileprintingrecords;
numbervar curr;
numbervar prev;
if groupnumber = 1 or
{inmain.nature} <> previous({inmain.nature}) then
0 else
curr-prev

-LB

 

[Zbrojovka]

That helped get rid of the outrageous numbers for the aforementioned case. What do I do if I still need to show the value for that though? If December had 1 call and January had 0 the formula will return a 0 when I'd like it to say -1. Is there a way to force the group to display the value even if it is null? Then it wouldn't matter if it were 0 since the calculation would still take place.

 

[lbass]

You cannot force a group to occur when there are no corresponding records. You could force the display formula to show a plus or minus value if there is only one group, I guess--do you want to do this?

-LB

 

[Zbrojovka]

Yes, if you'd be so kind...Forcing the compared integer with only one group would be perfect. So comparing 0 + 1 would show 1 or 1 + 0 would show -1

 

[lbass]

First create a formula {@month}:

date(year({inmain.calltime}),month({inmain.calltime}),1)

Then also create a reset formula for the Group #1 header:
whileprintingrecords;
numbervar curr := 0;
numbervar prev := 0;

Then change {@accum} to:
//{@accum} for the Group #2 section:
whileprintingrecords;
numbervar curr;
numbervar prev := curr;
if distinctcount({@month}, inmain.nature}) = 1 then
(
if maximum({@month},{inmain.nature}) = date(year(minimum({?daterange})), month(minimum({?daterange})), 1) then
prev := distinctcount ({inmain.inci_id},{inmain.calltime},"Monthly") else
if maximum({@month},{inmain.nature}) = date(year(maximum({?daterange})), month(maximum({?daterange})), 1) then
curr := distinctcount ({inmain.inci_id},{inmain.calltime},"Monthly")
) else
curr := distinctcount ({inmain.inci_id},{inmain.calltime},"Monthly");

And change this formula to:

//{@displdiff} for the Group #1 footer:
whileprintingrecords;
numbervar curr;
numbervar prev;
curr-prev

-LB

 

[Zbrojovka]

This is absolutely phenomenal!!! You put in so much work for this! Thank you very much. This was obviously above my skill grade and I owe you a big steak if you ever come to Ft. Worth, TX!!