aggregate function with case when condition in calculated field

Options
liuli
liuli Member
in Cards, Dashboards, Stories

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

Best Answer

  • Sam_Arigato
    Sam_Arigato Member
    Answer ✓
    Options

    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.

Answers

  • Sam_Arigato
    Sam_Arigato Member
    Answer ✓
    Options

    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
    liuli Member
    Options

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

Categories