Sum of a data table column only if the numbers in the column are positive

Help Studio
, ,
1

Hi all,

with the help of you guys I build up this LINQ formula that is checking if the values in the column are empty or not and then makes the sum:

math.Round(ML_FilteredDT.AsEnumerable().Where(Function(d) Not String.IsNullOrEmpty(d(“Gross Amount BC”).ToString) Or Not String.IsNullOrWhiteSpace(d(“Gross Amount BC”).ToString)).Sum(Function(a)Convert.ToDouble(a(“Gross Amount BC”).ToString)),2)

Now, I would like to add a check to sum only the positive values of the column. How should I do?

Thanks a lot in advance.

BR,
Franz

2

Hi @franz.verga ,

You can do this by just adding an AND condition along with the null value you are checking followed by cDbl(d(“Gross Amount BC”).ToString)>0

Regards,

3

Hi @franz.verga

how about the following?

math.Round(ML_FilteredDT.AsEnumerable().Where(Function(d) Not String.IsNullOrEmpty(d(“Gross Amount BC”).ToString) Or Not String.IsNullOrWhiteSpace(d(“Gross Amount BC”).ToString) And Convert.ToDouble(d(“Gross Amount BC”).ToString) > 0).Sum(Function(a)Convert.ToDouble(a(“Gross Amount BC”).ToString)),2)

Regards

4

Hi @franz.verga,

You may change the LINQ query to just add up positive values from the “Gross Amount BC” column by adding a highlighted condition in the where clause as following:

math.Round(ML_FilteredDT.AsEnumerable().Where(Function(d) Not String.IsNullOrEmpty(d(“Gross Amount BC”).ToString) AndAlso Not String.IsNullOrWhiteSpace(d(“Gross Amount BC”).ToString) AndAlso Convert.ToDouble(d(“Gross Amount BC”).ToString) > 0).Sum(Function(a) Convert.ToDouble(a(“Gross Amount BC”).ToString)),2)

Please note I have replaced the “Or” with “AndAlso” operator in where clause to make sure all 3 conditions must be true before proceed to sum.

Best Regards,
Priyank

1 Like
5

@franz.verga Hi. Try this. This will avoid throwing error if the value cannot be converted to double.
math.Round(ML_FilteredDT.AsEnumerable().Sum(Function(a) if(Double.TryParse(a(“Gross Amount BC”).ToString ,Result)=True andalso Result>0,Result,0)),2)

6

Hi @franz.verga ,

Maybe it can also be shortened to the below :

Math.Round(ML_FilteredDT.AsEnumerable().Where(Function(d) d("Gross Amount BC").ToString.IsNumeric andAlso CDbl(d("Gross Amount BC").ToString)>0).Sum(Function(a)CDbl(a("Gross Amount BC").ToString)),2)
7

Hi @franz.verga,

May I check if my solution was insightful to you, if yes please help to mark it as a solution, it would help!

Thank you.

8

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.