Solved

jQuery: JS: Calculate a percentage of two columns from the totals row

Posted on 2014-02-13
Medium Priority
1,265 Views
Hi,
I have a report that has subtotal rows and then a grand total row.
the report has several columns with two being INCOME, COST, and one being PERCENTAGE.

After report is complete I would like to show the
(INCOME subtotal - COST subtotal )/INCOME subtotal in the PERCENTAGE subtotal column

Income in the report as text with dollar sign and commas

\$221,144,127.16

one report has 4 subtotals and one grand total row
INCOME BREAK_COMPANY_2
INCOME BREAK_COMPANY_3
INCOME BREAK_COMPANY_4
INCOME BREAK_COMPANY_5
INCOME BREAK_COMPANY_5 (the grand total is the 2nd INCOME BREAK_COMPANY_5 - the only difference in the html is the color of the row )

Full Grand Total row html
``````<td class="apexir_AGGREGATE_VALUE" style="white-space: nowrap; background-color: rgb(112, 163, 224);" align="right" headers="Percentage % BREAK_COMPANY_5"></td>
``````
0
Question by:bcarlis
• 7
• 6

LVL 45

Expert Comment

ID: 39857478
Just a thought - if you're creating this report from data, can you not just build all the percatages at the same time
0

LVL 45

Expert Comment

ID: 39857481
To show you how to do this in client-side code, we're probably going to need to see a sample table so we know what we're working with
0

LVL 2

Author Comment

ID: 39857563
it is Oracle APEX so it is an IR canned report
0

LVL 45

Expert Comment

ID: 39857623
OK. Assuming it's generating valid HTML, can you view source and copy/paste into a document for us to see.
0

LVL 2

Author Comment

ID: 39857796
\$(this).text(parseFloat((rev -cost)/rev *100).toFixed(2)+'%');
})

this works great for the one value but I need to add a counter to replace the _2
0

LVL 45

Expert Comment

ID: 39857805
I'm working blind here, so if you won't show me the html (even if it has fake data) then I can't help you.

If I know the relationship of the various columns and rows, I can show you exactly what to do
0

LVL 2

Author Comment

ID: 39857810
Sorry Chris,

I figured it out.. thanks, this does it
I was looking for the command ideas like .each(function() - that got me going when I found that

var counter = 1;
var rev = parseFloat(\$('td.apexir_AGGREGATE_VALUE[headers="INCOME BREAK_COMPANY_'+counter+'"]').text().replace(/\\$|,/g, ''));
var cost = parseFloat(\$('td.apexir_AGGREGATE_VALUE[headers="COST BREAK_COMPANY_'+counter+'"]').text().replace(/\\$|,/g, ''));
\$(this).text(parseFloat((rev -cost)/rev *100).toFixed(2)+'%');
})
0

LVL 45

Expert Comment

ID: 39857827
OK.

There are probably easier ways of doing it, but I can't work blind!!

Good luck with it.
0

LVL 2

Author Comment

ID: 39858020
Well here is the html
The Grand Total is giving me trouble..
``````<tbody><tr><th colspan="14" class="apexir_REPEAT_HEADING" id="BREAK_COMPANY_1">Business Segment : <span class="apex_break_headers"><!--0--><!--0-->Advanced Technology Facilities</span></th></tr>
<tr style="display: table-row;"><th id="MARKET"><div style="text-align:center;">Market</div></th><th id="INCOMING_ORDERS_BUDGET"><div id="apexir_INCOMING_ORDERS_BUDGET" onclick="gReport.controls.widget(this.id)" style="text-align:center;">Incoming<br>Orders<br>Budget</div></th><th id="INCOMING_ORDERS"><div id="apexir_INCOMING_ORDERS" onclick="gReport.controls.widget(this.id)" style="text-align:center;">Incoming<br>Orders</div></th><th id="INCOME"><div style="text-align:center;">INCOME</div></th><th id="COST"><div style="text-align:center;">COST</div></th><th id="MARGIN"><div style="text-align:center;">Margin</div></th><th id="Percentage"><div style="text-align:center;">Margin<br>%</div></th><th id="BACKLOG"><div style="text-align:center;">Backlog</div></th><th id="INSIDE_SALES"><div style="text-align:center;">Inside<br>Sales</div></th><th id="OUTSIDE_SALES"><div style="text-align:center;">Outside<br>Sales</div></th><th id="PURSUITS"><div style="text-align:center;">Pursuits</div></th><th id="MSL"><div style="text-align:center;">MSL</div></th><th id="GAM"><div style="text-align:center;">GAM</div></th></tr>
</tbody>
``````
0

LVL 45

Accepted Solution

Chris Stanyon earned 2000 total points
ID: 39858047
OK. Now we can do something :)

Have a look at this. I've added a simple if statement to check if the percentage is a number.

``````\$('tr td.apexir_AGGREGATE_VALUE:first-child').parent().each(function() {
income = parseFloat(\$('td:eq(3)',this).text().replace(/[^0-9\.]+/g,""));
cost = parseFloat(\$('td:eq(4)',this).text().replace(/[^0-9\.]+/g,""));
percentage = (income - cost) / income * 100;

if (\$.isNumeric( percentage ) ) {
\$('td:eq(6)',this).text(percentage.toFixed(2) + '%');
} else {
\$('td:eq(6)',this).text('---');
}
});
``````
0

LVL 2

Author Comment

ID: 39858056
Thank you Chris..
sorry.. the NaN in the Grand Total is because the percentage is not calculated correctly..
I need to fix the calculation then show the percentage as the Sub-Totals..
Can we recalc that.. what am I missing?
Bill
0

LVL 45

Expert Comment

ID: 39858084
The code I've posted will calculate the percentage and display it fine for all the subtotal rows and the grand total row. The NaN that is showing in your page is overwritten by the jQuery calculation. If a calculation doesn't generate a number (as in the Business Segment : Undefined section) then it just inserts ---

You can see a working demo of it here:

http://jsfiddle.net/ChrisStanyon/zQ4HG/
0

LVL 2

Author Comment

ID: 39863715
yes Chris, it did do the job! once I removed existing highlight code.
Thank you soooo much..
beautiful!

Bill
0

Featured Post

Question has a verified solution.

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

In this article you'll learn how to use Ajax calls within your CodeIgniter application. To explain this, I'll illustrate how to implement a simple contact form to allow visitors to send you an email through your web site.
Today, the web development industry is booming, and many people consider it to be their vocation. The question you may be asking yourself is â€“ how do I become a web developer?
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn the basics of jQuery including how to code hide show and toggles. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQueryâ€¦
Suggested Courses
Course of the Month12 days, 17 hours left to enroll