Help for nested case statement in beast mode

user02072
user02072 Member
in Magic ETL

I have these two measures made in beast mode that looks like this:

Status 

CASE WHEN `Delivery` = 'Done' THEN 'Complete'
ELSE CASE WHEN IFNULL(`Quantity`, 0) < IFNULL(`Order Quantity`,0) THEN 'Pending' ELSE 'Complete 
END

Completion Percentage 

(
COUNT(DISTINCT 
CASE WHEN `Delivery` = 'Done' THEN CONCAT(`Company Code`, `Purchase Code`, 'Customer Code`)
ELSE CASE WHEN IFNULL(`Quantity`,0) = `Order Quantity` THEN CONCAT(`Company Code`, `Purchase Code`, 'Customer Code`) END
END) 
)
/ 
COUNT(DISTINCT CASE WHEN IFNULL(`Quantity`,0) < `Order Quantity` THEN CONCAT(`Company Code`, `Purchase Code`, 'Customer Code`) END)

Now the problem is that as you can see, the calculation for Completion Percentage only includes Case when `Delivery` =`Done`, while in the Status, the 'Complete` status also includes the Else condition. So how do I modify the code in Completion Percentage so that the Delivery also includes the Else condition like the one in the Status?

 

Thanks!

Comments

  • BlueRooster
    BlueRooster Domo Employee

    So a bit cleaner version of the Status calculation would look like this:

    CASE WHEN `Delivery` = 'Done' THEN 'Complete'
         WHEN IFNULL(`Quantity`, 0) < IFNULL(`Order Quantity`,0) THEN 'Pending' 
    	 ELSE 'Complete'
    END

    No need to nest the case statement here. And actually you could make it like this as well:

    CASE WHEN IFNULL(`Quantity`, 0) < IFNULL(`Order Quantity`,0) 
                           AND `Delivery` <> 'Done' 
               THEN 'Pending' 
    	   ELSE 'Complete'
    END

     

    And for your completion percentage calc, I'm assuming you're wanting the percentage of all 'items' that are below order quantity with a status of Complete. That would look like this:

    1 - 
    (
    COUNT(DISTINCT CASE WHEN IFNULL(`Quantity`, 0) < IFNULL(`Order Quantity`,0) 
                           AND `Delivery` <> 'Done' 
               THEN CONCAT(`Company Code`, `Purchase Code`, 'Customer Code`)	   
    END
    )
    /
    COUNT(DISTINCT CASE WHEN IFNULL(`Quantity`,0) < `Order Quantity` THEN CONCAT(`Company Code`, `Purchase Code`, 'Customer Code`) END)

    That code finds the % pending (the inverse of completion %) and does 1 - that value to give you completion.

     

    Hope that helps you get what you're looking for,

    Valiant

     

    **Please mark "Accept as Solution" if this post solves your problem
    **Say "Thanks" by clicking the "heart" in the post that helped you.

  • ST_Superman
    ST_Superman Domo Employee

    Just from a best practices approach, I would recommend moving the `Status` beast mode into the data set.  

     

    The advantage to placing any sort of "Classification" calculations directly into the data set is that you can then use that field in page filters as well as other beast modes.

     

    The Status calculation here is simply classifying each line of data by looking at the values of a few other fields in that line.  If there is no aggregation needed to define `Status`, or any other classification field, then there is no need to use a beast mode to calculate it.

     

    The completion percentage is a great use case for a beastmode as the beastmode will recalculate every time that you filter and slice the data a new way (by area, or quarter, etc)

Categories