?
Solved

How do I write a percentage formula in BO

Posted on 2011-02-11
1
Medium Priority
?
2,781 Views
Last Modified: 2013-11-16
I created 3 measures to display a summary in vertical table:

Count of Complete
Count of Incomplete
Count of  Learners

The details are below the summary table. There is a section break on Cost Center.

My Percentage formula is:
=Percentage([Count of Complete (PTD)]/[Count of Learners (PTD)])

The results returned is "1" for every Cost Center.

My previous formula:  
=Count([Learner ID]) Where([Sub-Qual Status]="Complete")/Count([Learner ID])*100+"%"
worked correctly, but did not display 0% for nulls.

How do write the correct percentage formula that will display the correct percentage per cost center, display "%" and display "0" in the column, if null?
0
Comment
Question by:KnightKreepa
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 35

Accepted Solution

by:
James0628 earned 2000 total points
ID: 34878079
It's hard to say what might be wrong with the first formula.  There could be a problem with one or more of your "Count of" measures.  I'm not familiar with the Percentage function.  BO 6.5 doesn't seem to have that.  There's a Percentile function, but that's something else.

 As for the old formula, I'm not sure what nulls you're referring to, but if the problem is that Count([Learner ID]) is null (that _would_ keep the formula from producing a result), you may just need to add an IsNull test.  Maybe something like:

=If IsNull (Count([Learner ID])) then 0+"%" Else Count([Learner ID]) Where([Sub-Qual Status]="Complete")/Count([Learner ID])*100+"%"


 FWIW, you can also leave off the '+"%"' and let the formula produce a numeric result, and then format the cell as a percentage.  If you do that, you don't need to multiply the result by 100.  The percentage format will take care of that.

 James
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
How to increase the row limit in Jasper Server.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

771 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