Subraction in Group

[Fsyeda]

I have two groups Sales rep and Year(Under sales rep group)
I get the sum of Sales and margin in two columns. I need to know how to insert a difference within each group for Sales and Margin.

Ex: Salesrep1 Sales Total Margin Total
2013 2000 300
2012 1200 200
(I want to have)Difference: 800 100

Salesrep2 Sales Total Margin Total
2013 1000 300
2012 5000 600
Difference: -4000 300

 

[Fsyeda]

Ex: Salesrep1
---------------------------------------SalesTotal------------MarginTotal
-----------------------------2013--------1000-----------------200
-----------------------------2012--------3000-----------------700

(I want to have)Difference:--------(-2000)-------------- (-500)

Salesrep2
---------------------------------------SalesTotal------------MarginTotal
-----------------------------2013--------5000-----------------200
-----------------------------2012--------3000-----------------100

(I want to have)Difference:--------(2000)-------------- (100)

I want to show this difference for each salesrep.

 

[MCuthill]

Fsyeda,

This looks like a Running Total solution. There is a wizard within Crystal that is likely sufficient for something like this, though I have personally never used it and prefer to setup the RT's manually. I would suggest taking a look at the wizard first, and if that is unable to help you, please reference below.

If you are looking for another example, I had provided this similar solution to another TekTipper in June: thread767-1712972

For your application, you will need one "set" of three formula's for each of your columns (to allow the most flexibility). The "RESET" will go in the Employee Header, with the "ACCUMULATION"s in the Group Header for Year, and the "DISPLAY" will be in the Employee Footer (as I understand your report). The following also assumes your report will always be presented as shown in the example (with the current year above the prior year - you will need to adjust the approach if Prior is always above Current).

Anywho, assuming all of the above, the solution would appear like follows: (there may be some fine tuning needed)

Formula Set #1 - SALES
{@RESET_SalesVarianceRT}

WhilePrintingRecords;
Shared NumberVar SalesVarianceRT:=0;

{@ACCUM_SalesVarianceRT}

Shared NumberVar SalesVarianceRT:=SalesVarianceRT - SUM({YourSalesField},{YourEmployeeIDGroupField)

{@DISPLAY_SalesVarianceRT}

WhilePrintingRecords;
Shared NumberVar SalesVarianceRT;

Formula Set #2 - MARGIN
{@RESET_MarginVarianceRT}

WhilePrintingRecords;
Shared NumberVar MarginVarianceRT:=0;

{@ACCUM_MarginVarianceRT}

Shared NumberVar MarginVarianceRT:=MarginVarianceRT - SUM({YourSalesField},{YourEmployeeIDGroupField)

{@DISPLAY_MarginVarianceRT}

WhilePrintingRecords;
Shared NumberVar MarginVarianceRT;

Hope this helps, or at least points in you in the right direction.

Cheers!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[Fsyeda]

It displays me the sum of the two fields instead of difference(with a negative sign). I have tried to tweak it but it never gives the difference. Running Total doesn't help either.

 

[pmax9999]

I tackled it this way.

Create the following formula and place in in GF2:

WhilePrintingRecords;

Global NumberVar S_Y1;
Global NumberVar S_Y2;
Global NumberVar M_Y1;
Global NumberVar M_Y2;


If      {Table.Year} = 2012
Then    (
            S_Y1 := Sum({Table.Sales},{Table.Year});
            M_Y1 := Sum({Table.Margin},{Table.Year})
        )
Else    (
            S_Y1 := S_Y1;
            M_Y1 := M_Y1
        );

If      {Table.Year} = 2013
Then    (
            S_Y2 := Sum({Table.Sales},{Table.Year});
            M_Y2 := Sum({Table.Margin},{Table.Year})
        )
Else    (
            S_Y2 := S_Y2;
            M_Y2 := M_Y2
        );

''

To display the Sales variation, create the following formula and place it in GF1:

WhilePrintingRecords;

Global NumberVar S_Y1;
Global NumberVar S_Y2;

S_Y2 - S_Y1

To display the Margin variation, create the following formula and place it in GF1:

WhilePrintingRecords;

Global NumberVar M_Y1;
Global NumberVar M_Y2;

M_Y2 - M_Y1

Hope this helps.

Cheers
Pete

 

[Fsyeda]

Somehow I managed to get the below query working for my report. I played around with the data a lot and ended up with this Formula working for me. I still do not understand the logic though it works perfectly for my query !

Sales Variance : (Sum ({tablename.Salesfield}, {tablename.Employeefield})) - (2 * (Sum ({tablename.Salesfield}, {tablename.Yearfield})))

Margin Variance : (Sum ({tablename.Marginfield}, {tablename.Employeefield})) - (2 * (Sum ({tablename.Marginfield}, {tablename.Yearfield})))

I will try Pete's Solution too and see if it works. Thanks for the inputs everyone

 

[pmax9999]

Interesting. If your formulas give you the results you need my suggestion can't be what you are looking for. I must have missed something.

Cheers
Pete

 

[Fsyeda]

I have tried your code Pete and it works perfect with my report.