# aggregate function with case when condition in calculated field

Member

Hi, have a question for how aggregate function with case when condition in calculated field.

1. domo dataset follow attached schema/data,
2. then create a Single Value card from this dataset,
3. then I created a filter Team=team1

I want to archive:
have a calculated field , use total of col Old_Col before 2023-04-01( should be 2) , or use col New_Col after 2023-04-01( should be 2) , get the sum of this, to divide Count(Distinct(`ID`)) ( should be 7) , to get a rate, as 4/7 =0.57 .

Here is how I write the calculated field:
Name: Failed_Rate
Calculation: (CASE WHEN `ReleasedDate` >= '2023-04-01T00:00:00' THEN SUM(`New_Col`)
WHEN `ReleasedDate` < '2023-04-01T00:00:00' THEN SUM(`Old_Col`)
END)/Count(DISTINCT(`ID`))

Name: Total_Failed
Calculation: (CASE WHEN `ReleasedDate` >= '2023-04-01T00:00:00' THEN SUM(`New_Col`)
WHEN `ReleasedDate` < '2023-04-01T00:00:00' THEN SUM(`Old_Col`)
END)

But it displays something odd:
1. why in Data Table the value is 0.29, but Card display 2.4?
2. Total_Failed should be 4, not 2

I think somehow my calculated field as Failed_Rate not correct, but I am not sure how to fix it.

Thank you for any help

• Member

Good Morning Liuli,

Try this…

SUM(CASE WHEN `ReleasedDate` >= '2023-04-01T00:00:00' THEN `New_Col`
WHEN `ReleasedDate` < '2023-04-01T00:00:00' THEN`Old_Col`
END)

The reason your original calculation did not work is because a CASE statement is granular and a SUM statement is aggregate. Using aggregate statements inside granular statements is very unpredictable.

Hope this helps!

Samantha

Brought to you by Arigato Analytics.

• Member

Good Morning Liuli,

Try this…

SUM(CASE WHEN `ReleasedDate` >= '2023-04-01T00:00:00' THEN `New_Col`
WHEN `ReleasedDate` < '2023-04-01T00:00:00' THEN`Old_Col`
END)

The reason your original calculation did not work is because a CASE statement is granular and a SUM statement is aggregate. Using aggregate statements inside granular statements is very unpredictable.

Hope this helps!

Samantha

Brought to you by Arigato Analytics.

• Member

@Sam_Arigato , you solution works!
it totally saved my day and thank you so much for you quick response.