Solved

Percentage dilema

Posted on 2008-10-30
26
457 Views
Last Modified: 2011-10-19
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
DFOCR:=(DFOC+DCR); //Adding both running totals
 
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.

Can someone please help me getting the percentages correctly?

Regards

Jehanzeb
0
Comment
Question by:jehanzebn
  • 14
  • 8
  • 4
26 Comments
 
LVL 17

Expert Comment

by:MIKE
ID: 22845935
What PERCENTAGE is currently displaying...ANYTHING? or are you getting errors?
0
 

Author Comment

by:jehanzebn
ID: 22848379
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
 
LVL 17

Expert Comment

by:MIKE
ID: 22850750
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 Comment

by:jehanzebn
ID: 22851260
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
 
LVL 17

Expert Comment

by:MIKE
ID: 22851508
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 Comment

by:jehanzebn
ID: 22851721
No no, the formulas are placed in the group footer . here is how it is made up

please see attached photo.

Regards

Jehanzeb


ADMIN EDIT - TEXT FILE REMOVED (SENSITIVE INFORMATION)
0
 
LVL 17

Expert Comment

by:MIKE
ID: 22851783
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
 
LVL 100

Expert Comment

by:mlmcc
ID: 22852050
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 Comment

by:jehanzebn
ID: 22865430
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
 
LVL 100

Expert Comment

by:mlmcc
ID: 22869773
What does the raw data look  like?

Are daily jobs included in last5 days jobs?

mlmcc
0
 

Author Comment

by:jehanzebn
ID: 22941302
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
 
LVL 100

Expert Comment

by:mlmcc
ID: 22945144
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 Comment

by:jehanzebn
ID: 22948319
 
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 dont 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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 100

Expert Comment

by:mlmcc
ID: 22952991
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 Comment

by:jehanzebn
ID: 22957991
Ok here it is.



LabFOCRDetailedReport.txt
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 22963169
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 Comment

by:jehanzebn
ID: 22963296
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
 
LVL 100

Expert Comment

by:mlmcc
ID: 22963415
YOu can set up functions like

If (yourCriteria) then
    {yourvalue}
else
     0

These can then be used in summaries.

mlmcc
0
 
LVL 100

Expert Comment

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

mlmcc
0
 

Author Comment

by:jehanzebn
ID: 22963461
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 Comment

by:jehanzebn
ID: 22974139
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 Comment

by:jehanzebn
ID: 22974378
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
 
LVL 100

Expert Comment

by:mlmcc
ID: 22977124
How are you setting the summary as a %?

You can simply change the display by editting the summary.

mlmcc
0
 

Author Comment

by:jehanzebn
ID: 22984169
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 Comment

by:jehanzebn
ID: 22985655
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
 

Accepted Solution

by:
jehanzebn earned 0 total points
ID: 23043231
Please close the question this has been sorted.

many thanks

Regards

Jehanzeb

0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now