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 derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How to calculate percentage manually

Status
Not open for further replies.

jehanzebn

Programmer
May 28, 2001
244
Dear all, I have a report which is running total Lenses of two types and both of them are using conditional counting. For example

Total Lenses 1 is counted when Linetype = C and
Total Lenses 2 is counted when Linetype = F.

I have tried using the following formula but this does not give me the right percentage.

Code:
Whileprintingrecords;
Numbervar CRLenses=0;
CRLenses:={#CRLensTotal};
CRLenses%Count ({foccredsum.stock_code});
totext(CRLenses%Count ({foccredsum.stock_code}),2)+"%"

How can I use the above formula to get a right percentage i.e. a conditional one.

Please note: Count(foccredsum.stock_code) will not give the right results until or unless Condition Linetype=C is set.

So I need to set this condition in the formula to get the right results.

The grand running total, sub total and running total are running fine.

Is it possible?

Many thanks

Regards

Jehanzeb
 
Ahh again an over complicated post put on here and on Sam ;)

To perform a manual percentage calculation simply remember the basic math.

Stop resorting to shared variables for each and every formula unless they are required :) A lot of the time the same functionality can be acheived without doing so.

//{@Count Cs}
If {table.linetype} = 'C' then 1 else 0

//{#Total C}
Sum -> {@Count Cs}

Reset never etc...

//Manual Percentage
{#Total C} / {#CRLensTotal} * 100



'J

CR8.5 / CRXI - Discovering the impossible
 
Why are you using a variable for this? Are you sure running totals are necessary? How are the running totals set up?

If you don't have row inflation, you could use a formula {@typeC} like this:

if {table.Linetype} = "C" then 1

Then you could use a formula like the following:

{#CRLensTotal} % sum({@typeC})

...for a grand total percentage. Not sure at what level you want the percentage. You could potentially use a conditional formula instead of {#CRLensTotal}.

-LB
 
Hello and thanks for your quick response.

The suggestion you have provided did not work for some reason.

Here is what I did
Code:
If {foccredsum.line_type} = "C" 
then 
1 
else 
0

Then

Code:
Sum({@CountCRLens})

Then

Code:
If {#CRLensTotal}=0 then
0
else
{@SumCRLens} / {#CRLensTotal} * 100

I have to say CR Total =0 to make it not divide by 0.

It gives me big numbers like 49660, 25000, 3453 etc instead of percentages.

Regards

Jehanzeb.
 
You are actually not providing enough information. Please show some sample data at the detail level along with the summaries you expect to see based on that data. Also, you did not explain how you set up the running total.

-LB
 
Morning LB,

Right this is how the report is setup. Please note: I do not wish to have percentages for Sub total and grand total Only the running totals for the group 2.

screenshotpercentagesneeded.jpg


What I have done is that I have setup each Running total like this.

Total Jobs (FOC)
Field to summarize: foccredsum.doc_no
Summary type: Distinct Count
Evaluate: Formula - {foccredsum.line_type}="F"
Reset: On change of Group 2

Total Jobs (CR)
Field to summarize: foccredsum.doc_no
Summary type: Distinct Count
Evaluate: Formula - {foccredsum.line_type}="C"
Reset: On change of Group 2

Total Lenses (FOC)
Field to summarize: foccredsum.qty
Summary type: SUM
Evaluate: Formula - {foccredsum.line_type}="F"
Reset: On change of Group 2

Total Jobs (CR)
Field to summarize: foccredsum.Stock_Code
Summary type: Count
Evaluate: Formula - {foccredsum.line_type}="C"
Reset: On change of Group 2

Group By - Reason Code
Report Section formula is set with date range like this:
Code:
// testing date range and code range
{foccredsum.date_created} in date(2008,09,01) to date(2008,09,29) and
{foccredsum.reason_code} in 1 to 999

Now with the sub total and grand total what I have done:

Sub Total - Reset on Group 1
Grand Total - No Reset.

When I used the formula provided above, it does bring 1s and 0s in my detail section of the report. Which is correct however the percentage comes in big numbers.

I think I have to use manual running totals to make all of this work?

Or can I use these automatic running totals and get conditional percentages?

Many thanks

Regards

Jehanzeb
 
Here is a little test I did and found out why the 1s formula given above is giving wrong percentage.

I created a new report with foccredsum and created a formula like this

Code:
IF {foccredsum.line_type}="C" then
DistinctCount({foccredsum.doc_no}) Else
0;
and placed it in the details section.

The totals which were coming in the list weren't correct, instead of coming as 665 total it was coming up as 1152. I know 1152 is all the records (un filtered) where as 665 are filtered records.

So when I use the above formula the percentage is taken from all the records i.e.
Reason Code 1 = 10 - 0.87% out of 1152.

How can I filter the report according to Linetype c and f and divide each record to get my percentages.

Regards

jehanzeb
 
Ok I hit a problem here.

The method I used is as follows

I created a formula:

Counter
--------
If Linetype="F" then 1 else 0


I entered this into Details section of the report.

Then I added another formula into the report footer (to test the total jobs)

GCounter
---------
Whileprintingrecords;
Sum(Counter)


The total number comes up to the same amount as of total lenses and not total jobs.

Example

Total jobs = DistinctCount of Foc.docnum when linetype="F" = 665

Total Lenses = Sum of Foc.Orderqty when linetype="F" = 1126

If I use the formula above, I get the answer 1126 and not 665.

1126 is right for lenses,however how can I get total jobs?

I tried to use

GCounter
--------
Whileprintingrecords;
Count(Counter)

but that give 1162 not 665.

How can I use this Counter so that it counts the jobs?

Many thanks

Regards

Jehanzeb
 
By sample data I meant for you to show how the data displays in the detail section so we could see if there is data that duplicates. Then also show the resulting percentage you would expect to see based on the sample data. Showing the design view is not sufficient.

-LB
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top