I have reports that some fields are empty. When the rows contain no blank or empty fields the sum totals work perfectly.
My question: Is there a way to ignore those empty fields and still achieve the sum total of the column? Any suggestions or comments are well appreciated.
Below are the examples of my concern:
1. No empty fields, the column totals work.
2. Rows with empty fields, the column totals fail.
What if you add a .default formatter to make nothing 0 rather than nothing, you may also have to add a tonumber formatter
Can you briefly describe how you create the sum?
Thank you!
<td>{{sc_dor_test.data.list_report_test_new.sum(`offering_amount.toNumber()`).formatCurrency("$", ".", ",", "2")}}</td><!-- offering_amount -->Maybe you can show me what you mean by using the line of code below as an example of your suggestion?
The class ‘default’ has boggled my mind this morning. Ha!
<td>{{sc_dor_test.data.list_report_test_new.sum(`offering_amount.toNumber()`).formatCurrency("$", ".", ",", "2")}}</td><!-- offering_amount -->Maybe just try:
<td>{{sc_dor_test.data.list_report_test_new.sum(`offering_amount.default(0).toNumber()`).formatCurrency("$", ".", ",", "2")}}</td><!-- offering_amount -->
Ah! I will... and post the results here. Thanks!
No change. It still fails. Could it be due to the joined tables that return a row of empty data be the cause?
Or can a Ternary Op be used in a case like this?
Can you check the browser console for errors/warnings please? Are you sure you even need the toNumber formatter there?
Yes, I’ve done that and there are no errors showing; with the exception of the typical ‘Not Found’ .map errors.
When I run a query that returns rows that contain data in each column the sum works perfectly. It’s only when the columns are empty from the Left Join tables. That’s why I suspect the empty columns are preventing the sum from calculating and returning the Invalid Amount or NaN.
Are you sure you pasted this code exactly? Notice the .default(0) part
Yes, each was entered exactly as shown.
<td>{{sc_dor_test.data.list_report_test_new.sum(`offering_amount.default(0).toNumber()`).formatCurrency("$", ".", ",", "2")}}</td><!-- offering_amount -->Please send me a link to your page where i can check what’s going on.
Let me remove the login restrictions, etc. first and then I will post the link to you.
Maybe before that you can try this, without the toNumber:
{{sc_dor_test.data.list_report_test_new.sum(`offering_amount.default(0)`).formatCurrency("$", ".", ",", "2")}}
That is it! It works on all of them… except one.
The only exception is the ‘NaN’ that is returned on this column:
<td>{{sc_dor_test.data.list_report_test_new.sum(`auto_miles.default(0).formatNumber(1, ".", ",")`)}}</td><!-- auto_miles -->
I think it is the misplacement of the `) after the formatNumber...Ok, please send me (in a private message) a link with some login details so I can check this.
Yes! They all working now.
The error was that I had placed the closing quote & parenthesis `) on the outside of the .formatNumber(1, “.”, “,”).
After I moved the `) to this:
<td>{{sc_dor_test.data.list_report_test_new.sum(`auto_miles.default(0)`).formatNumber(1, ".", ",")}}</td><!-- auto_miles -->
It now works!
Thanks Teodor and the others for your direction and guidance!So adding the .default(0) actually replaces the empty columns with a ‘phantom 0’ when there is no data from the query?
Yes, that’s right.