Percentage formula
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=10931
Printed Date: 01 May 2024 at 4:26pm
Topic: Percentage formula
Posted By: ianwells
Subject: Percentage formula
Date Posted: 24 Aug 2010 at 12:14am
|
Can a sum be calculated using a COUNT field as i cant seem to get it right, maybe i'm way off the ball on this though.. formula i'm trying is this.... sum({@weeks } / {Count of HSW.FormDate }) * 100 Can anyone point me in the right direction and bear in mind i'm very new to crystal reporting. Cheers Ian  |
Replies:
Posted By: DBlank
Date Posted: 24 Aug 2010 at 3:48am
|
sum({@weeks })%Count({HSW.FormDate })
or your version would be
(sum( mailto:%7b@weeks - {@weeks }) / Count({HSW.FormDate })) * 100
|
Posted By: ianwells
Date Posted: 24 Aug 2010 at 9:32pm
|
Cheers DBlank How will it look if i need it reversed, the COUNT first then @weeks? (COUNT({HSW.FormDate})/{@weeks}) *100 ? |
Posted By: DBlank
Date Posted: 25 Aug 2010 at 3:45am
|
(COUNT({HSW.FormDate})/SUM( mailto:%7b@weeks - {@weeks })) *100
or
COUNT({HSW.FormDate})%SUM( mailto:%7B@weeks - {@weeks })
you also may want ot deal with the possibility of zero denominator which will give you an error 'cannot deivide by zero'
if SUM( mailto:%7B@weeks - mailto:%7b@weeks%7d%29=0 - {@weeks })=0 then 0 else COUNT({HSW.FormDate})%SUM( mailto:%7B@weeks - {@weeks })
|
Posted By: ianwells
Date Posted: 25 Aug 2010 at 9:06am
|
Thanks DBlank I must be doing something wrong i appear to get the same result for every record even though i know they should be different.. The formula does use the result of the COUNT and said formula @weeks and divide to show a percentage? the result of @weeks is 10 and COUNT of HSW.FormDate is 8 and should show 80% but i get the result 141 for every record....any ideas where i may be going wrong? Cheers Ian |
Posted By: DBlank
Date Posted: 25 Aug 2010 at 9:13am
|
both of your formulas are looking at the totals for all records (not grouped levels). if you want to look at grouped data you need to use sumamries that are at the group level. these are constructed generally as:
COUNT(field,groupfield)
SUM(field,groupfield)
Does that address it?
|
Posted By: ianwells
Date Posted: 25 Aug 2010 at 10:04pm
| Yes it makes sense but still a little lost how to do that! |
Posted By: DBlank
Date Posted: 26 Aug 2010 at 3:33am
|
What is your group structure including field names and for which groups are you trying to get the percentages? |
Posted By: ianwells
Date Posted: 26 Aug 2010 at 10:16pm
|
This counts the total of reports for a contract ControlofFormDate2: Count of HSW.FormDate divided by a result of formula @weeks which is datediff ("w", {CON.OnSiteStart},currentdate) these are placed in my group footer #3. |
Posted By: DBlank
Date Posted: 30 Aug 2010 at 3:53am
| What is the field that is being grouped on to create group level 3? |
Posted By: ianwells
Date Posted: 06 Sep 2010 at 11:10pm
|
Group footer 3# HSW.FormDate -A CountofFormDate1:Count of HSW.FormDate |
Posted By: DBlank
Date Posted: 07 Sep 2010 at 4:35am
|
I will assume this a date field and it is set to group at a weekly interval so try:
if SUM( mailto:%7B@weeks - mailto:%7b@weeks%7d%29=0 - {@weeks })=0 then 0 else COUNT({HSW.FormDate},{HSW.FormDate},"weekly")%SUM( mailto:%7B@weeks - {@weeks })
|
Posted By: ianwells
Date Posted: 16 Sep 2010 at 12:20pm
|
Sorry its been a while.... i get an error... "There must be a group that matches this field" COUNT({HSW.FormDate},{HSW.FormDate},"weekly") this section above is highlighted |
Posted By: DBlank
Date Posted: 17 Sep 2010 at 3:49am
| create a group using FormDate and set it to weekly |