Syntax Error

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)))

Answers

  • @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)
    

  • @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? 

  • @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)

  • @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?

  • @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__cis 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)