Jan 16 2019 09:11 AM - edited Jan 16 2019 09:12 AM
I have a SharePoint list that I populate from an Excel file via SharePoint Rest API. Then, I have added sum of all the columns in the list. It works perfectly up till here. However, in the top most row of the list where sums are shown I need to show a value by dividing sum of one of the columns by another. This value is supposed to change with the list filters.
I have been scratching my head for the last two days to achieve this but all in vain. I will appreciate any help. I will provide further details if required. Thanks
Jan 17 2019 08:04 AM
Please help, or at least let me know if its possible or not.
Jan 17 2019 10:02 AM
Jan 17 2019 01:45 PM
Thank you so much for your reply Carlis. I really appreciate it. Actually the case is like this: I want to divide sum of column A by sum of column B and I want to show it on top of column C. These sums of Column A and Column B are calculated by SharePoint and shown on top of each Column. The row with sums does not actually exist in the list. It is created at run time. How can I achieve this division and show it in Column C. Screen Shot is attached for a clearer picture.
Jan 18 2019 04:17 AM
Jan 18 2019 01:51 PM
Thanks Carlis.... I don't mind coding at all. I will do whatever it needs. Just let me know how can I do it. Thanks
Jan 21 2019 10:18 AM
SolutionHey Kjay! I took a glance at your screenshot and assumed a few things. First, you are using the Totals tool from the list view to display the numbers on the top, right? Second, that is a classic list view, aka classic layout. Third, you are using jQuery(I can see some nice formatting so I assume you are using it to fix stuff). If either one of these are not true, this may not work for you, tell me so we can try to work this out.
Now, the solution. If you open the Developer Tools and inspect the ribbon that shows the totals, you may see that it's actually a <tbody> tag appended to the table that shows the results, before the tbody that actually shows the results. This ribbon is a <tr> tag with a static ID, so it was pretty easy to query it using jQuery.
The function I included in the attachment basically picks the text of each one of the Totals row, calculate the average and set the value to the Average column.
All you need to do is call it and provide the index of the columns containing the Total, the Sum, and the Average columns. It's important to note that the checkmark that shows when you hover the title of a row and the 3 dots("...") for the options do count as columns, so you have to include those to figure the index of your columns.
Seeing your screenshot I would guess your columns A, B, and C are at indexes 4, 9, and 13. Call the function I provided and see if it works for you.
One thing to note is that this function only changes the values when it is called, so you may want to call it every time the view changes.
Well, I am having trouble uploading the code so I will just paste it here. Lemme know if you need anything. I would suggest opening your developer tools and testing the code before inserting it to the page to see if it works.
function calcAverage(sum_column_index, total_column_index, average_column_index){
var rows = $('#aggWPQ1 td');
var hash = rows.map((i, target) => {return {name: $(target).text().split("=")[0], value: $(target).text().split("=")[1]}});
var sum = parseFloat(hash[sum_column_index].value.replace(/[^0-9.-]+/g,""));
var total = parseFloat(hash[total_column_index].value.replace(/[^0-9.-]+/g,""));
var avg = (sum / total).toFixed(2);
rows.eq(average_column_index).html("<strong>" + hash[average_column_index].name + '= ' + avg + "</strong>")
}
Hope it helps,
Jan 22 2019 10:07 AM
Thank you so much for your great help Carlis. I will try it out and get back to you in a day or two. Thanks again... really appreciate it.
Jan 23 2019 01:43 PM
Works like a charm!!!!!... thank you so much for your help Carlis... I really appreciate it so much. You are a lifesaver....
Jan 24 2019 04:02 AM
Jan 21 2019 10:18 AM
SolutionHey Kjay! I took a glance at your screenshot and assumed a few things. First, you are using the Totals tool from the list view to display the numbers on the top, right? Second, that is a classic list view, aka classic layout. Third, you are using jQuery(I can see some nice formatting so I assume you are using it to fix stuff). If either one of these are not true, this may not work for you, tell me so we can try to work this out.
Now, the solution. If you open the Developer Tools and inspect the ribbon that shows the totals, you may see that it's actually a <tbody> tag appended to the table that shows the results, before the tbody that actually shows the results. This ribbon is a <tr> tag with a static ID, so it was pretty easy to query it using jQuery.
The function I included in the attachment basically picks the text of each one of the Totals row, calculate the average and set the value to the Average column.
All you need to do is call it and provide the index of the columns containing the Total, the Sum, and the Average columns. It's important to note that the checkmark that shows when you hover the title of a row and the 3 dots("...") for the options do count as columns, so you have to include those to figure the index of your columns.
Seeing your screenshot I would guess your columns A, B, and C are at indexes 4, 9, and 13. Call the function I provided and see if it works for you.
One thing to note is that this function only changes the values when it is called, so you may want to call it every time the view changes.
Well, I am having trouble uploading the code so I will just paste it here. Lemme know if you need anything. I would suggest opening your developer tools and testing the code before inserting it to the page to see if it works.
function calcAverage(sum_column_index, total_column_index, average_column_index){
var rows = $('#aggWPQ1 td');
var hash = rows.map((i, target) => {return {name: $(target).text().split("=")[0], value: $(target).text().split("=")[1]}});
var sum = parseFloat(hash[sum_column_index].value.replace(/[^0-9.-]+/g,""));
var total = parseFloat(hash[total_column_index].value.replace(/[^0-9.-]+/g,""));
var avg = (sum / total).toFixed(2);
rows.eq(average_column_index).html("<strong>" + hash[average_column_index].name + '= ' + avg + "</strong>")
}
Hope it helps,