CountIf function in Domo

dylang91
dylang91 Member
in Magic ETL

Hello I have a data that contains all the cases submitted and their submission date and in another column it has the approval date which can be in another date/year. I am trying to get the conversion rate (approvals/submissions) for each month, because of the timing they might not happen in the same month.

A pivot table fails to do this since it only has counts it under the same period, but a count if formula is able to do this but I am having a hard time to recreate this on DOMO.

I am trying to recreate the green area


conversion rate problem.png


Tagged:

Best Answer

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    Hi @dylang91

    You'll need to use an ETL unpivot your data such that your data is in the following format:

    CaseID | Date | Type

    243339 | 2/10/2021 | Submission

    243339 | 2/10/2021 | Approved

    ...


    Then you can use a beast mode to calculate your percentages per month:

    CASE
WHEN COUNT(CASE WHEN `Type` = 'Submission' THEN `CaseID` END) = 0 THEN 0
ELSE 
    COUNT(CASE WHEN `Type` = 'Submission' THEN `CaseID` END) / COUNT(CASE WHEN `Type` = 'Approved' THEN `CaseID` END)
END

    Submissions per month:

    COUNT(CASE WHEN `Type` = 'Submission' THEN `CaseID` END)

    Approvals per month:

    COUNT(CASE WHEN `Type` = 'Approved' THEN `CaseID` END)


    And plot using your Date field.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**

Answers

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    Hi @dylang91

    You'll need to use an ETL unpivot your data such that your data is in the following format:

    CaseID | Date | Type

    243339 | 2/10/2021 | Submission

    243339 | 2/10/2021 | Approved

    ...


    Then you can use a beast mode to calculate your percentages per month:

    CASE
WHEN COUNT(CASE WHEN `Type` = 'Submission' THEN `CaseID` END) = 0 THEN 0
ELSE 
    COUNT(CASE WHEN `Type` = 'Submission' THEN `CaseID` END) / COUNT(CASE WHEN `Type` = 'Approved' THEN `CaseID` END)
END

    Submissions per month:

    COUNT(CASE WHEN `Type` = 'Submission' THEN `CaseID` END)

    Approvals per month:

    COUNT(CASE WHEN `Type` = 'Approved' THEN `CaseID` END)


    And plot using your Date field.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • MarkSnodgrass
    MarkSnodgrass Coach

    You can actually do this in the beast mode and setting your date range filter to group by month of submission date as it sounds like you don't care which month the approval occurred in, just the fact that it was approved. You can get the approvals count by doing a beast mode like this:

    SUM(CASE WHEN `ApprovalDate` IS NOT NULL THEN 1 ELSE 0 END)

    Then to do a conversion rate, your beast mode would look like this:

    SUM(CASE WHEN `ApprovalDate` IS NOT NULL THEN 1 ELSE 0 END)
/
COUNT(`CaseID`)

    Finally, changing your date range filter to graph by month will make your date column display in the format of 2021-Nov, which I believe is what you want.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • dylang91
    dylang91 Member

    @GrantSmith Thank you so much! It took me a second to unpivot the information but it worked like a charm! I appreciate it!

  • dylang91
    dylang91 Member

    @MarkSnodgrass I actually needed to identify how many cases were approved this month, which is why I was struggling, because it would only count the ones that were submitted the same month but not the month that were approved. Thank you! :)

Categories