# Percentage dilema

I have created a report which calculates Jobs and lenses. The report shows Daily jobs, last 5 days jobs, last 4 weeks jobs and year to date jobs (lenses as well). All of them are working.

Now I would like to show percentages however when I try to use a formula it doesn't seem to be working properly.

Here is how I setup my report.

Group Header 1 - This group is Reason Code in specified format
Group Header 2 - this group is Reason Code in ascending format
Group Footer 2 - Jobs, Lenses, Jobs, Lenses, Jobs, Lenses, Jobs, Lenses "(starting from Daily, Last 5 days, Last 4 Weeks and Year to date)"
Group Footer 1 - Used for Sub total of group footer 2 data
Report Footer - Used to show Grand Totals of the data

Now to calculate Jobs, I have to create a formula like this,
I will show only one (daily job( because I need to get an idea how to get percentage work!, once idea is clear then I can apply to others.

Calculating Jobs formula, sub total and grand total

Whileprintingrecords;
Numbervar DFOC:=0; //DFOC means Daily Free of Charge
Numbervar DCR:=0; //DCR means Daily Credit
Numbervar DFOCR:=0; //DFOCR means Daily Free of charge+Credit
Numbervar DFOCRS; //DFOCRS means Daily FOC+Credit Subtotal
Numbervar DFOCRG; //DFOCRG means Daily FOC+Credit GrandTotal

DFOC:={#DailyFOCJobTotal}; //Running Total of FOC
DCR:={#DailyCRJobTotal}; //Running Total of Credit

DFOCRS:=DFOCRS+DFOCR; //Subtotal
DFOCRG:=DFOCRG+DFOCR; //Grand Total

DFOCR; //Total of FOC +Credit

Sub Total Code

// This subtotal is getting reset using a reset formula in group header 1
Whileprintingrecords;
Numbervar DFOCRS;

Grand Total

Whileprintingrecords;
Numbervar DFOCRG;

Now I tried to get the percentage by doing the following code but it didn't seem to work

1st way of getting percentage

Whileprintingrecords;
If Numbervar DFOCR=0 then
0
else
Numbervar DFOCR /  Numbervar DFOCRG * 100;

2nd way of getting percentage

Whileprintingrecords;
If {@Daily FOC+CR JobTotal}=0 then 0 else
{@Daily FOC+CR JobTotal} / {@Daily FOC+CR Job GrandTotal} * 100

3rd way of getting percentage

Whileprintingrecords;
Numbervar Percentage:=0;
Percentage:={Daily FOC+CR JobTotal};
Percentage%DistinctCount({Foccredsum.doc_no};
Totext(Percentage%DistinctCount({Foccredsum.doc_no},2)+ " % ";

None of the 3 ways are working

Example Report

Marketing and Sales (This is Group Header 1)
600 - 0 (This is Group header 1 with records on the Group footer)
608 - 2
689 - 0

ST -- 2 (Sub total - placed in Group footer 1)

Inventory
700 - 0
712 - 1
789 - 0

ST -- 1

GT -- 3 (Grand total placed in Report footer)

ST means Sub Total, GT means Grand Total

The above scenario under Marketing and Sales produces 100%, whereas Inventory produces 33.33%.

The Inventory percentage is fine if you look at it because 1 / 3 * 100 means 33.33% whereas 2 /3 * 100 means 66.66% but it states 100% instead of 66.66% for some reason under Marketing and sales.

Regards

Jehanzeb
###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Software Solutions ConsultantCommented:
What PERCENTAGE is currently displaying...ANYTHING? or are you getting errors?
0
Author Commented:
Hello and a very warm morning,

I am getting percentages however for some reason it is taking the percentage from the yearly Grand total instead of taking it from its own grand total.

For example,

Last 5 days counter shows 2 Jobs under Marketing and Sales, the grand total of Last 5 days is 3, so in actual fact the percentage should be 66.66%. The Grand total of Year to date counter is 79.
What my report is doing is, it is showing the percentage as 2.53% which is 2/79 instead of 2/3.

The problem I am having is that, even using a formula where I am dividing the Last 5 days counter with last 5 days grand total, the report is still showing the percentage wrong.

I have tried a formula like this, however it did not work either.

Whileprintingrecords;
Numbervar L5DJobs:=0; //Variable declared
Numbervar L5DTotal:=0; //variable declared
L5DJobs:={@L5D FOC+CR JobTotal}; //records count formula
If {foccredsum.date_created} in ((Minimum(last7days))-1) to((Maximum(Last7days))-1) then
L5DTotal:=DistinctCount({foccredsum.doc_no}); //This is where I tried to define the date range which it should look at but not working.
If L5DTotal>0 then
L5DJobs%L5DTotal
else
0;

Here in the above formula I tried to distinctly make the total to be according to the date range but it still doesn't work.

Could it be because I am calculating everything under one group? but if that was the case my results shouldn't have come up correctly. Why is it only with percentage that I am not getting it correctly.

Regards

Jehanzeb
0
Software Solutions ConsultantCommented:
Remember to be mindful of "where" you are placing this formula for calculation. Could change the totals based on WHERE it is placed and what is being processed into the calculation at that time.

0
Author Commented:
Yeah I have placed them in Group footer 2 which is set as ascending order. The Totals are getting grouped by Group 2 however when I am using the percentage it just doesn't seem to work.
Even though I have tried the following formula still it produces the percentage from the Year to date.

I  suspect it is because I setup Date under Record Selection formula as YTD. but if I don't set it, the whole report runs from 1900s and I only want it to run from Year to date, last 4 weeks, l5 days rolling and daily.

Any ideas?

Regards

Jehanzeb
0
Software Solutions ConsultantCommented:
Separate the Variables into their OWN formulas that are SET to zero in the group header,. then accumulated in DETAILS..(or wherever appropriate)..then a final formula to DISPLAY the results.

From what I can tell, the FACTORS within the formula calc are all based on DETAILED COLUMNS of data right? is the formual placed within the DETAILS Section as well?

0
Author Commented:
No no, the formulas are placed in the group footer . here is how it is made up

Regards

Jehanzeb

ADMIN EDIT - TEXT FILE REMOVED (SENSITIVE INFORMATION)
0
Software Solutions ConsultantCommented:
Here is what I'm saying...I don't "think" that thi part of the formula works when placed ONLY in the group footer...I think you need to accumulate this somehow within the DETAILS...THEN use the total...within the GROUP FOOTER>..

If {foccredsum.date_created} in ((Minimum(last7days))-1) to((Maximum(Last7days))-1) then
L5DTotal:=DistinctCount({foccredsum.doc_no}); //This is where I tried to define the date range which it

I could be wrong, but it appears to me that you are analyzing ONLY the last record in the GROUP with this DATE logic....

?????
0
Commented:
Are you trying tp get a % of the total?  In that case you need to use summary functions because you won't know the total numbers until all groups are done.

mlmcc
0
Author Commented:
Hello mlmmc,

I am trying to get the percentages of each Job and len which are based on Daily, Last 4 weeks, last 5 days and year to date.

For example (just showing jobs because of lack of space however lenses include too)

Daily Jobs - Last 5 days Jobs - Last 4 weeks Jobs - year to date Jobs
5 - 10%   -         18 - 20%       -         98 45%              -         435 - 12%

Now the 10% is coming from 5 / Grand Total Daily Jobs
Similarly, 20% is 18/Grand Total of Last 5 days jobs
45% is 98 / Grand total of last 4 weeks jobs
12% is 435 / Grand total of year to date jobs.

What is happening here is that my percentages for all the jobs and lenses are coming from

Daily Job / Grand total of year to date job
L5D / Grand total of year to date job
L4W / Grand total of year to date job
YTD / Grand total of year to date job

So each job is divided by the grand total of year to date instead of getting divided by their respective grand totals.

That is the problem.

I am not getting the total percentage instead I am getting the percentage of each job out of their respective grand total.

You can see my report setup in the image attached.

Regards

Jehanzeb
0
Commented:
What does the raw data look  like?

Are daily jobs included in last5 days jobs?

mlmcc
0
Author Commented:
Hello and sorry for the delayed reply. I have been stuck with the same issue for so long. Apperently db admin didn't fix the issue with the daily jobs however now it has been fixed.

Saying that, the percentage is still not working.
mlmcc, The report does include daily jobs in the last 5 days jobs as well. The raw data is correct.

I am getting the records perfectly as I have created 3 different Groups for them. The Running Totals are counting the correct number of records.

As I have two Running totals for each job (Credit Job + Free of Charge Job), these two jobs are getting added up in the report by using a  formula

For example
#################################################################
*YTD* formula
Running total of Credit Job + Running Total of Free of Charge Job = Total Jobs
#################################################################
*Last 5 Days* formula
Running total of Credit Job + Running Total of Free of Charge Job = Total Jobs
##################################################################
*Last 4 Weeks* formula
Running total of Credit Job + Running Total of Free of Charge Job = Total Jobs
##################################################################
*Current Date* formula
Running total of Credit Job + Running Total of Free of Charge Job = Total Jobs
##################################################################

To get the grand total of these records, I have created another formula which counts all the above YTD records, L5D records etc and displays them in the report footer. Grand Total is showing up correctly.

Now, What I want is the percentage of each record, i.e. the percentage of YTD records, L5D records respectively .

I have created a formula which counts the percentage but incorrectly.

Here is the formula

Whileprintingrecords;
Numbervar b:=0;
numbervar n:=0;
numbervar m:=0;

b:={@TotalJobsFOCRL5D}; // this is total job, (Credit+Free of charge jobs)
n:={@TotalJobsFOCRL5DGT}; //GT is the grand total showing in report footer
If b=0 then 0 else //if total jobs = 0 then answer is 0
m:=(b/n)*100; // divide total jobs with grand total
totext(m,2)+"%"; //show as percentage

This formula does show the percentage but divides each job by itself. So if Total Jobs = 9 then it shows 100% whereas Grand Total is 479.

The percentage formula is placed on the respective group footer.

Kind Regards

Jehanzeb
0
Commented:
I need to see the other formulas or if you could upload the report that would be even better.  To upload make a copy and change the extension to TXT.

mlmcc
0
Author Commented:

Mlmcc, I would upload the file providing that I can delete it at the later stage? The reason being that I do not wish to share company data without their permission; unless we can delete it at later stages then I dont mind uploading it. Additionally do you have CR 2008? because the file is created in CR 2008 SP0.

Alternatively I can try and explain the situation by posting the formula below?

Running Total Daily:
=================
Summarize field: foccredsum.doc_no
Summary Type: Distinct Count
Evaluate: {foccredsum.line_type}="C" and
{foccredsum.date_created} in currentdate to currentdate
Reset: Group 2

Last 5 days running total:
==================
Summarize field: foccredsum.doc_no
Summary Type: Distinct Count
Evaluate: {foccredsum.line_type}="C" and
{foccredsum.date_created} in ((Minimum(last7days))-1) to((Maximum(Last7days))-1)
Reset: Group 3

Last 4 weeks running total:
==================
Summarize field: foccredsum.doc_no
Summary Type: Distinct Count
Evaluate: {foccredsum.line_type}="C" and
{foccredsum.date_created} in (Minimum(Last4WeeksToSun)) to ((Maximum(Last4WeeksToSun))-2)
Reset: Group 4

Year to date running total:
==================
Summarize field: foccredsum.doc_no
Summary Type: Distinct Count
Evaluate: {foccredsum.line_type}="C" and
{foccredsum.date_created} in year to date
Reset: Group 5

Formulas:

Formula to count Total Jobs Daily, last 5 days, last 4 weeks, year to date (The concept is exactly the same with l5d,l4w and ytd):
Whileprintingrecords;
Numbervar x:=0;
Numbervar y:=0;
Numbervar z:=0;

x:={#TotalFOC};
y:={#TotalCR};
z:=x+y;
z;

Formula placed on each group footer is like this (similar for l5d, l4w and year to date).

Whileprintingrecords;
Numbervar TJs;
Numbervar TotalJobs=0;
Numbervar CAvg=0;
TotalJobs := {@TotalFOCR};
CAvg:= TotalJobs;
TJs:=TJs + CAvg;
CAvg

Formula placed onto each group footer is like this (this is the one which does not reset, this is to count the grand total, however the concept is exactly the same). Above is TJs (meaning Total Jobs, here it is TJGs, meaning Total Jobs Grand)

Whileprintingrecords;
Numbervar TJGs;
Numbervar TotalJobs=0;
Numbervar CAvg=0;
TotalJobs := {@TotalFOCR};
CAvg:= TotalJobs;
TJGs:=TJGs + CAvg;
CAvg

Reset Formula:

Whileprintingrecords;
Global Numbervar TJs:=0;
Global Numbervar TLs:=0;
Global Numbervar TJL5Ds:=0;
Global Numbervar TLL5Ds:=0;
Global Numbervar TJL4Ws:=0;
Global Numbervar TLL4Ws:=0;
Global Numbervar TJYTDs:=0;
Global Numbervar TLYTDs:=0;

SubTotal: This formula is placed in the Group 1 footer to count the sub total

Whileprintingrecords;
Numbervar TJs;

Grand total formula. This is placed in the Report Footer

Whileprintingrecords;
Numbervar TJGs;

All of the above is working fine, the problem comes when I take their percentage. The percentage for each one is coming out of the YTD grand total instead of their own respective grand totals. That is the main problem. I have tried number of ways to get the percentage to work but it does not work. I have placed each percentage under Group Footer 2.

Here is the formula to count percentage (please note below are two formulas)
Formula 1:

Whileprintingrecords;
Numbervar Jobs=0;
Jobs:={#TotalCR};
Jobs%DistinctCount ({foccredsum.doc_no});

Formula 2:
Whileprintingrecords;
Numbervar Jobs=0;
Jobs:={#TotalFOC};
Jobs%DistinctCount ({foccredsum.doc_no});

This is the percentage formula I placed on the report, Group footer.

Formula 3:
Whileprintingrecords;
Numbervar g:=0;
Numbervar f:=0;
Numbervar d:=0;
g:={@Jobs Percentage FOC};
f:={@Jobs Percentage CR};
d:=g+f;
totext(d,2)+"%"

Regards

Jehanzeb
0
Commented:
I do have CR2008.  Let me look this over.

I believe I can delete the file later or if necessary get a mod to do it.
If you save it without data then there shouldn't be any issues but then we won't be able to test changes.

mlmcc
0
Author Commented:
Ok here it is.

LabFOCRDetailedReport.txt
0
Commented:
The problem is you are basing these on running totals.

A running total only has the final total at the end of the report.

I haven't looked at this totally but you need to use summary functions to get the final total early enough to calculate intermediate percentages.

mlmcc
0
Author Commented:
The reason why I am using running total is because I have to setup the date ranges for each running total. If you look at my formulas for running total (Evaluate stage) it states, Line_type="C" and date_created in currentdate to currentdate.
Similarly with last 5 days, last 4 weeks etc.

I am not sure how to use summary function to set the date range and line type and yet count the grand total.

Any ideas?

Many thanks

Regards

Jehanzeb
0
Commented:
YOu can set up functions like

If (yourCriteria) then
{yourvalue}
else
0

These can then be used in summaries.

mlmcc
0
Commented:
Summaries put in a group footer will be for those groups.
Summaries can be displayed as a % of the total.

mlmcc
0
Author Commented:
I did try the crietria as you suggested, however it didn't make a difference when it comes to 4 different date crietria.

I am not in my office however I will try this on Monday, being in UK it's bit different to where you might be (I am guess USA?).

Many thanks

Regards

Jehanzeb
0
Author Commented:
Morning mlmcc,

I have tried your idea of getting the Summary into the group footer. Actually it shows the same distinct Count which I have done manually by using formulas however it doesn't resolve the issue of percentage. As soon as I set it up as percentage, it takes the percentage from the grand total of Year to Date.

Any further ideas which I can try?

Regards

Jehanzeb
0
Author Commented:
whileprintingrecords;
Numbervar FOCRP;
FOCRP:={@TotalFOCRT};
If FOCRP=0 then 0;
If {foccredsum.date_created} in currentdate to currentdate then
FOCRP%{@TotalJobsFOCRGT}
Else
0;

This is the formula I tried but this doesn't seem to be working, for example it is showing results like this

1 - 1/1 = 100%
2 - 2/3 = 66.66%
3 - 3/6 = 50%
----
6

It's getting Grand total as Cumulative and dividing each record with cumulative manner. How can I make the Grand Total to be 6(in the above case) which then gets divided by individual record?

Regards
0
Commented:
How are you setting the summary as a %?

You can simply change the display by editting the summary.

mlmcc
0
Author Commented:
Yeeehaaaaaa!!! It has been sorted now!

I can't believe this. I had to write a code like this

{@L4WTotalLens}/{@L4WGrandTotalLens}*100

I just can't believe this. It is working now.

Many thanks for your great help mlmcc.

Many thanks

Regards

Jehanzeb
0
Author Commented:
Ok this has not been sorted to be honest. The last numbers are getting divided by their grand totals and not the first records.

Here is how I re-setup the report.

I created two Groups, 1 Group which shows the Names of the Reason Code. Second Group Reason Code in ascending order.

The Running Total are setup like below.

Counting CR Job

Field to summarize: foccredsum.doc_no
Summary type: DistinctCount
Evaluate - Formula:{foccredsum.line_type}="C" and
{foccredsum.date_created} in Currentdate to Currentdate
Reset: Group 2

Counting FOC Job
Field to summarize: foccredsum.doc_no
Summary type: DistinctCount
Evaluate - Formula:{foccredsum.line_type}="F" and
{foccredsum.date_created} in Currentdate to Currentdate
Reset: Group 2

Counting SUB CR Job
Field to summarize: foccredsum.doc_no
Summary type: DistinctCount
Evaluate - Formula:{foccredsum.line_type}="C" and
{foccredsum.date_created} in Currentdate to Currentdate
Reset: Group 1
Counting Sub FOC Job
Field to summarize: foccredsum.doc_no
Summary type: DistinctCount
Evaluate - Formula:{foccredsum.line_type}="F" and
{foccredsum.date_created} in Currentdate to Currentdate
Reset: Group 1

Counting Grand Total CR Job

Field to summarize: foccredsum.doc_no
Summary type: DistinctCount
Evaluate - Formula:{foccredsum.line_type}="C" and
{foccredsum.date_created} in Currentdate to Currentdate
Reset: none

Counting Grand Total FOC Job

Field to summarize: foccredsum.doc_no
Summary type: DistinctCount
Evaluate - Formula:{foccredsum.line_type}="F" and
{foccredsum.date_created} in Currentdate to Currentdate
Reset: none

Similar running totals for all three Last 5 days, last 4 weeks and year to date except the evaluation for them is changed. For example Yeartodate would be

Year to date

Field to summarize: foccredsum.doc_no
Summary type: DistinctCount
Evaluate - Formula:{foccredsum.line_type}="F" and
{foccredsum.date_created} in Yeartodate
Reset: Group 2

Last 5 days
Field to summarize: foccredsum.doc_no
Summary type: DistinctCount
Evaluate - Formula:{foccredsum.line_type}="F" and
{foccredsum.date_created} in ((Minimum(last7days))-1) to((Maximum(Last7days))-1)
Reset: Group 2

Last 4 Weeks
Field to summarize: foccredsum.doc_no
Summary type: DistinctCount
Evaluate - Formula:{foccredsum.line_type}="C" and
{foccredsum.date_created} in (Minimum(Last4WeeksToSun)) to ((Maximum(Last4WeeksToSun))-2)
Reset: Group 2

Group2 is set to ascending order of Reason Code.Group1 is set to Specified format, i.e. named Reason Code

Then I created formulas to get Total Jobs by adding the two together.

Code for setting up Total Jobs CurrentDate - Record
//Daily Total Job
({#DailyCRJob}+{#DailyFOCJob})

This is placed on the Group Footer 2 (which is set to ascending).

Code for setting up Sub Total Jobs CurrentDate
//Daily Sub Total Jobs
({#DailySubCRJob}+{#DailySubFOCJob})

This is placed in the Group 1 which is set to Specified format

Code for setting up Grand Total Jobs CurrentDate
//Daily Grand Total Jobs
({#DailyGrandCRJob}+{#DailyGrandFOCJob})

This is placed in the Report Footer

Similarly, all others are setup accordingly, with same concept above.

Now I created 2nd section on Group Footer 2. So it looks like Group Footer 2A has Records, and Group Footer 2B has percentages.

These percentages don't work out properly. That is the main problem. Even though I am using Running Totals accordingly, the percentage comes from the last record and not from rest of the records. For example

First record - 2
2nd record - 3
3rd record - 5

Let's say Grand Total = 500 for that running total.

Now when you run the report it produces, 100% 60% and 1%.

Which means it is dividing initially by 2, then 5 and then 500. So the last record is getting divided by 500 but not the above two.

The report should be taking percentage from their own respected Grand totals through out the report and not just last record.

I hope I made some sense here now?

Regards
0
Author Commented:
Please close the question this has been sorted.

many thanks

Regards

Jehanzeb

0

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.