Running total for a percentage of sum field 1

[Randygk]

I am trying to use a running total of a percentage of sum field to see the total percentage of sales of products up to that item.

 

[putts]

I think the easiest way to accomplish this is to have a formula field on each of your records that is set to a 1 of item has been sold and a 0 if not. (we'll call the field @isSold for this example)

then, in each record, have another formula field that performs this:

Sum(@isSold)/Count({Any Database Field})

That should work.

 

[Randygk]

I probably did not describe the sum correctly the percentage is $ of item sold/Grand total of item sold and then items are sorted in descending order by percentage

 

[lbass]

You could do this in one of two ways:

1-Create a running total {#salesamt} using the running total editor:

Select {table.salesamt}, sum, evaluate for each record, reset never or on change of group (if you are evaluating at a group level). Add this to the details section.

Then create a formula {@percentoftotal}:

{#salesamt} % sum({table.sales},{yourgroupfield})//or eliminate ", {yourgroupfield}" if you have no groups

Place this in the details section to display the cumulative percent.

2-Create a variable which will accumulate the summaries. Let's assume one group (on salesperson) in the following:

Create {@resetcumulpercent} and place in the group header:

whileprintingrecords;
numbervar cumulpercent := 0;

Create {@cumulpercentdetail} and place in the detail section:

whileprintingrecords;
numbervar cumulpercent := cumulpercent + {table.salesamt} % sum({table.salesamt},{table.salesperson});

Either approach should work.

-LB

 

[Randygk]

When I try the 2nd approach I use {#salesamt} PercentOfSum ({p21_order_view.extended_price}, {p21_order_view.item_id}) where this is the percent of the sum of group #1. I get an error when I save the formula "The remaining text does not appear to be part of the formula.

 

[kenhamady]

I don't think you will be able to sort on the percentage. You can only "sort" groups using a TopN, and this requires a summary field. You can't use a formual of sumamry fields in TopN.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com

 

[putts]

Not sure how much control you have over your data tables, but this could be accomplished through simple views.

For instance, I have a table with all my company's employees listed including those that have been terminated.
The terminated field is a 1 or a 0, so I can easily build one view that simply does a SUM([Terminated]) AS Total_Count_of_Fires and just returns a count of the total number of fired employees.

I then create a new query which groups the Employees table by department, grabs only fired employees and then does a count on the ID field to get the number of fired employees for that department.

I then add that first view I built into this new one (no need to link any fields for this use) and add that Total_count_of_fires field.

So, my end result looks something like:

Department | # of Fires | Total # of Fires
Marketing | 3 | 25
IT | 12 | 25
Executives | 2 | 25
HR | 8 | 25

Using this data for your example, I could sort by the # of fires field and then easily do a percentage by divided #ofFires/Total#ofFires.

This really isn't a CRYSTAL fix persay, but it might help do that sort you needed.

 

[Randygk]

IT Worked!!! Thanks for all the help I ended up using an edited version of lbass' solution #1. The formula for percent of total I used is: "{#salesamt} / Sum ({p21_order_view.extended_price}) * 100"

 

[putts]

DRAT!

All my creative Database Genius to no avail.

Well, the important thing is that it's working. Ü