CountIf function in Domo

dylang91

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

GrantSmith

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.

GrantSmith

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.

MarkSnodgrass

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.

dylang91

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

dylang91

@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! :)