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.

 

[beltmanjr]

Just create a formula reading

sum({1st field to sum},{group 1 field}) - sum({2nd field to sum},{group 1 field})

and place it in the report footer

 

[beltmanjr]

hmm, my above reply does sum1 - sum2.
Simply reverse the order for sum2-sum1

 

[lbass]

I think you would have to set up a variable like this to be placed in a group section:

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

-LB

 

[kinosaidp]

I'm sorry but I'm not getting the result in the suggested code.

If I would use the sum function Sum(fld, condFld, cond), it would be something like;

Sum(mydata, mygroupfield, "mygroupfield = x") - Sum(mydata, mygroupfield, "mygroupfield = y")

which does not allow me to embed a where clause in the third arguement for "mygroupfield = x"(or y).

I know simple it is to run a summary but getting the difference is really interesting.

thanks.

 

[kinosaidp]

Reply below worked. Thank you all!

*********************************
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

 

[Zbrojovka]

Hi,

I'm having this exact same problem and been trying to work it out for quite awhile. I think this may be the perfect solution, but I need a little more explaining of the formula structure to better understand how to replicate it. My scenario is exactly the same. Just need to know exactly what Ibass means by {table.group}, {table.amt}, and "Instance1"

Much appreciated!!

 

[beltmanjr]

He means the field name on which the grouping is done.

So place into the group footer the formula

whileprintingrecords;
numbervar A;
numbervar B;

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

Place in report footer

whileprintingrecords;
numbervar A;
numbervar B;
B - A

This will subtract your findings for your first group from your second group

 

[Zbrojovka]

I'm sorry to be so ignorant with this one, but my brain has been fried today. So I've created the formula as described:

whileprintingrecords;
numbervar A;
numbervar B;

if ({inmain.calltime}) = "Instance1" then
A := sum({inmain.inci_id},{inmain.calltime});
if ({inmain.calltime}) = "Instance2" then
B := sum({inmain.inci_id},{inmain.calltime})

BUT I'm getting an error on the "Instance1" area. What is supposed to go here? Based on my elements it's asking for a Date/Time field.

Basic Idea of what I'm trying to do:

Group #2 December 2007 1 <-----Sum of incidents in {?DateRange}
January 2008 5

Total: 6 Difference: ?

 

[lbass]

Your first instance is December. Try this:

whileprintingrecords;
numbervar A;
numbervar B;

if month({inmain.calltime}) = 12 and
year({inmain.calltime}) = 2007 then
A := sum({inmain.inci_id},{inmain.calltime});
if month({inmain.calltime}) = 1 and
year({inmain.calltime}) = 2008 then
B := sum({inmain.inci_id},{inmain.calltime});

...but why would you be summing an ID field? I wonder whether you really need a distinctcount or a count.

-LB

 

[Zbrojovka]

I apologize...it is a distinct count of inmain.inci_id.

For the Instance part, that needs to be dynamic as the months/years will change based on user selection. The above formula would definitely work if it where a static value.

 

[beltmanjr]

Where would the month and year values come from?
And what would the input look like?

 

[Zbrojovka]

The Month and Year are displayed based on a group for {inmain.calltime}. I've formatted it to only display the Month Year but is actually a Date/Time Field grouped by (#2){inmain.calltime}.
I have a parameter {?DateRange} for the user to enter multiple range values. In this case we have entered the two values:
2007-12-01 00:00:00 through 2007-12-31 23:59:59 and;
2008-01-01 00:00:00 through 2008-01-31 23:59:59

Then I've done a distinctcount ({inmain.inci_id}) which tells me how many of that incident occurred in the date range under Group # 1. So the total output looks like:

Disturbance General
December 2007 1
January 2008 5
Total Calls: 6 Difference between calls: ??

 

[beltmanjr]

And now you wish to check how many of that incident occurred for each date range and find the difference??

I would use two parameters to make live easier. Perhaps it is possible with the multiple ranges and if so I'd be curious to read how to access the different parts of the ranges for something like this (hint to LBass)

I have created parameter r1 & r2 for this example

whileprintingrecords;
numbervar A;
numbervar B;

if month({Orders.Shipped}) >= month(minimum({?r1})) and
  year({Orders.Shipped}) >= year(minimum({?r1})) and 
  month({Orders.Shipped}) <= month(maximum({?r1})) and
  year({Orders.Shipped}) <= year(maximum({?r1}))
  then
    A := sum({inmain.inci_id},{inmain.calltime});

if month({Orders.Shipped}) >= month(minimum({?r2})) and
  year({Orders.Shipped}) >= year(minimum({?r2})) and 
  month({Orders.Shipped}) <= month(maximum({?r2})) and
  year({Orders.Shipped}) <= year(maximum({?r2}))
  then
    B := sum({inmain.inci_id},{inmain.calltime});

 

[lbass]

If you have a group on month, and you want the difference between the current group value and the previous group value, use a formula like this:

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

-LB

 

[Zbrojovka]

Morning all.

Well I've tried both of your recommendations with no success so far.

Beltman I tried using your idea of multiple params, but the select criteria debunked the data:
{inmain.city} = "ECV" and
{inmain.calltime} = {?PrevMonth} or
{inmain.calltime} = {?RptMonth} and
not {inmain.cancelled}

LBass, I worked with your formula as it was presented and tweaked it a little on my own and got results, but not the right ones. These have all been really good ideas and avenues, but just not quite right yet.

I think I've confused you with the grouping. Let's just pretend group 2 is the only group, which is the calltime. So we just need to find the difference of the distinctcount on the values for group 2. LBass your formula is close to what I need so if we can tweak that to factor only what I need I think it may work.

Basically I just need to know if calls increased or decreased from month to month.

 

[lbass]

Well, I guess you will have to share exactly how you implemented my formula, because your tweaks might have broken it.

-LB

 

[Zbrojovka]

Your formula by itself didn't display the correct values and incorporated group #1. I believe it probably didn't work because of the inclusion of group #1. So I just played with the logic to try and re create a similar formula which also did not work. So my tweaking was after the fact.

 

[lbass]

I tested this with two groups, and it worked fine, so please show the formula that you used.

It might also help if you showed a sample of the results you got with my formula, so I can troubleshoot it. Not sure what you mean by it incorporating group #1.

Please also identify your group #1 field and your Group #2 field.

-LB

 

[Zbrojovka]

Im sure it works perfect with two groups. But I only need something that works with one group. I have two groups in play, but group #1 has no counts on it. Group #2 for calltime which displays December and January plus the distinct count on that group. Here's an example of my output again:

Disturbance General <------Group # 1
December 2007 1 <------Group # 2 (Monthly) with distinctcount
January 2008 5
Total Calls: 6 Difference between calls: ??

Using your formula exactly as you have given it gives the result as 5.00 for the difference.

What I would like displayed is the value for January - December which would tell me there were 4 more calls in January than December. But remember the Months are dynamic and will change based on the users selection in the parameter {?DateRange}. In the cases where calls have decreased I would like the negative integer displayed, such as: -4 indicating four less calls happened.