Calculation in subtotal 1

[jraheja]

Hi,

I want to do a calculation in the group footer section, when 2 summations are done in that footer. The third

e.g Year1 Year2 Perc diff
100 Club A $200 $100
100 Club b $300 $100
SUBTOTAL $500 $200

I want the perc diff Sum(Year1)-Sum(Year2)/Year2 * 100 to be calculated on the SUBTOTAL fields $500 and $200 not a total of the Perc diff column. How do I enforce that?

Crystal Reports 10 is what I am using.

 

[lbass]

Create a formula:

(sum({@Year1},{table.group}) - sum({@Year2},{table.group}))
% sum({@Year2},{table.group})

-LB

 

[synapsevampire]

You might need to check for zero in @year2 as well:

if sum({@Year2},{table.group}) <> 0 then
(sum({@Year1},{table.group}) - sum({@Year2},{table.group}))
% sum({@Year2},{table.group})
else
0

-k

 

[jraheja]

I tried to simplify the data here. In the header I have each record as a summation. The subtotal is the summation of these. So how should the formula change?

 

[lbass]

You shouldn't simplify the problem, as I guess you can see, since different solutions might work for a simplified version that wouldn't work for a more complex problem.

The solution now depends on how you created the original summary at the Club level. Did you just insert summaries? If so, then the earlier solutions should still work, since they are just summing across a higher level group. So how did you get the subtotal in your example above? Also, are $500 and $200 inserted summaries or did you have to use variables?

-LB

 

[jraheja]

The club level is a summary of detail transactions.

The subtotal is a summary of those. They are summations. The difference will be the formula I displayed. This is what I use at the header level to calculate percentage difference -

if Sum ({@YTD_Prev_Year_Sales}, {@Merge Club}) <> 0 AND Sum ({@YearEntered_YTD_ Sales}, {@Merge Club}) <> 0
then perc:= (Sum ({@YearEntered_YTD_ Sales},{@Merge Club})-Sum ({@YTD_Prev_Year_Sales}, {@Merge Club}))/Sum ({@YTD_Prev_Year_Sales}, {@Merge Club}) * 100
else if Sum ({@YTD_Prev_Year_Sales}, {@Merge Club}) = 0 AND Sum ({@YearEntered_YTD_ Sales}, {@Merge Club}) = 0
then perc:=0
else if Sum ({@YTD_Prev_Year_Sales}, {@Merge Club}) = 0
then perc:=100
else if Sum ({@YearEntered_YTD_ Sales}, {@Merge Club}) = 0
then perc:=0;

For e.g 2004 sales 2003 sales % change
1000 2000 -50%
2000 500 300%
Subtotal 3000 2500 20%

I have to get the 20% based on the totals in the Subtotal and not the column total of percentage column. That is the correct difference.

 

[lbass]

If your result is at the report footer level, then create another formula that removes the group condition {@Merge Club}. If you want this result at an outer group level, then change {@Merge Club} to the outer group field.

-LB

 

[jraheja]

I did that and it gives me the last record's percentagect percentage and I can't figure what I am doing wrong. I even tried creating a variable to sum at the header level and use that at the footer level. What did you mean by outer group level.

 

[lbass]

Sorry, you don't really need to be using a variable in these formulas, and that's probably what messed it up when I said to just replace the fields.

If your result row is at the report level then use:

if Sum ({@YTD_Prev_Year_Sales}) <> 0 AND
Sum ({@YearEntered_YTD_ Sales}) <> 0 then
(Sum ({@YearEntered_YTD_ Sales})-
Sum ({@YTD_Prev_Year_Sales}))/Sum ({@YTD_Prev_Year_Sales}) * 100 else
if Sum ({@YTD_Prev_Year_Sales}) = 0 AND
Sum ({@YearEntered_YTD_ Sales}) = 0 then
0 else
if Sum ({@YTD_Prev_Year_Sales}) = 0 then
100 else
if Sum ({@YearEntered_YTD_ Sales}) = 0
then 0

You said that your club rows were already groups, so your subtotal row is either a total for a higher order group, i.e., outer group, or it is really a grand total (at the report level). Not sure which it is.

-LB

 

[jraheja]

My grand total is different - report footer. The subtotal is at the group footer and club total is at group header level and is a merge of clubs total. The detail is suppressed. Let me try and see. I had tried your approach but in a diff way but your took the formula approach while I defined a variable to calculate a subtotal at the header level. But mine did not work. Gave me incorrect results. Some other urgent report has come so I have to keep this aside for some time. Will let you know whatever the case.. Thanks!

 

[jraheja]

Thanks a lot. That helped a lot.