aggregate function with case when condition in calculated field

liuli

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

dora_result.png

domo_ds_lee-test.xlsx

Sam_Arigato

Good Morning Liuli,

Try this…

SUM(CASE WHEN ReleasedDate >= '2023-04-01T00:00:00' THEN New_Col
WHEN ReleasedDate < '2023-04-01T00:00:00' THENOld_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.

Sam_Arigato

Good Morning Liuli,

Try this…

SUM(CASE WHEN ReleasedDate >= '2023-04-01T00:00:00' THEN New_Col
WHEN ReleasedDate < '2023-04-01T00:00:00' THENOld_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.

liuli

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