Syntax Error

Member

Can someone help me with this formula? I need to show the number of X12_Month_Scheduled_Review__c - IF the Session_Date__c is THIS MONTH. Not all Session Dates will have a Scheduled Review Date.

IF(`Session_Date__c` = 'THIS MONTH',
THEN(COUNT(`X12_Month_Scheduled_Review__c`)))

• Coach

@Stucker Domo does not have an IF function like in Excel, instead it uses CASE statements for logical operations (see this list of supported beast mode functions: https://domo-support.domo.com/s/article/360043429933?language=en_US). Also when aggregating data using a sum or count, it is generally better to put those outside of the case statement like below:

```count(case when `Session_Date_c` = 'THIS MONTH' then `X12_Month_Scheduled_Review__c` end)
```

• Member

@MichelleH - thank you for that information! Unfortunately the formula is not pulling in the correct count - its pulling in 0.. they are both date fields so I wonder if that has something to do with it?

• Coach

@Stucker If they are both date fields, then we would need to set up the logic criteria to compare to the month and year of today's date. As it was written before, it would only count the `X12_Month_Scheduled_Review__c` field if the `Session_Date_c` field contains the words “THIS MONTH”. Here is how you can rewrite it:

`count(``case`` ``when`` MONTH(Session_Date_c) = MONTH(CURRENT_DATE()) and YEAR(Session_Date_c) = YEAR(CURRENT_DATE()) ``then`` X12_Month_Scheduled_Review__c ``end``)`

• Member

@MichelleH - Question for this one is there a way to rework this formula to only show when ‘Session_Date’ is this month and ‘X12_Month_Scheduled_Review__c’ IS NOT BLANK?

• Coach

@Stucker The formula should already account for both of those criteria.

This checks whether the Session_Date_c field has the same month and year as today's date:

`case`` ``when`` MONTH(Session_Date_c) = MONTH(CURRENT_DATE()) and YEAR(Session_Date_c) = YEAR(CURRENT_DATE())`

For each row that meets the above condition (having a session date this month), it returns the `X12_Month_Scheduled_Review__c` field. Rows that do not meet the condition return a null value.` `

`then`` X12_Month_Scheduled_Review__c ``end`

Rows where `X12_Month_Scheduled_Review__c`is null are NOT counted in the `COUNT()` function, so the result of the entire formula should only include rows where both criteria are met.

If you formula is not calculating as expected, then it's possible that the `X12_Month_Scheduled_Review__c` field contains empty strings instead of null values, in which case you can add an additional condition to your formula:

`count(``case`` ``when`` MONTH(Session_Date_c) = MONTH(CURRENT_DATE()) and YEAR(Session_Date_c) = YEAR(CURRENT_DATE()) and X12_Month_Scheduled_Review__c <> ''``then`` X12_Month_Scheduled_Review__c ``end``)`

` `