Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Running total for a percentage of sum field 1
- Thread starter Randygk
- Start date
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.
then, in each record, have another formula field that performs this:
Sum(@isSold)/Count({Any Database Field})
That should work.
- Thread starter
- #3
-
1
- #4
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
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
- Thread starter
- #5
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.
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
- tek@kenhamady.com
Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Guide to Crystal in VB
- tek@kenhamady.com
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.
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.
- Thread starter
- #8
- Status
Similar threads
- Locked
- Question
- Locked
- Question
- Locked
- Question
- Locked
- Question