Calculating Total with Condition

[Heather]

Hi, I'm trying to use Totals and Aggregations to calculate the total of the records. But I want to have a condition.
For example,  IF the 'Company' is equal to 'Caspio' that it would total the contents of the 'Balance' column.

[JolliBeng]

Hi @Heather,

That can easily be done in Totals and Aggregations.

You need to add a "Total aggregate" field on a Search and Report DataPage and select "Formula" radio button and place the below code:

SUM(CASE WHEN Company='Caspio' THEN Balance else 0 end)
 

-JolliBeng
 

[kpcollier]

What if we are trying to add a condition in another way? I need to Sum a Hours column, where another column (Earn_Code) = 1, and another aggregate where Earn_Code = 2.

I also have grouping enabled here. 

I was hoping I could just do SUM(Hours) WHERE Earn_Code = 1, but this is invalid. It seems I need to use a SELECT statement, which would not take the Grouping filter or the dates from the search form into account.

[cheonsa]

Hello @kpcollier,

You can add a calculated field in the Reports. Then have a CASE WHEN condition that will check if the Earn_Code is equal to 1. If yes, display the value of the Hours. 

CASE WHEN Earn_Code = 1 THEN [@field:Hours] ELSE 0 END

In the Totals and Aggregation, select this Calculated field and select Sum function.

Lastly, you can just hide the Calculated field so it will not be visible in the report.

 

Hope this helps.

[cheonsa]

For hiding of column in a Tabular report, you can check this post:

 

[Harpreet]

To have the aggregation based on conditions we can use flow like this

SUM(IIF(JOB_FUNCTIONS LIKE '%ASSEMBLY%', [@calcfield:8], 0))