Skip to content

    • Magic ETL

    Magic ETL

    Nested IF AND for Columns in a Pivot Table

    Member
    in Magic ETL

    Hi,
    I have an IF AND nested Excel formula I am trying to recreate in Domo. I want to create a new calculated field that classifies the order submission activity for the prior whole 7 months. This is what the result would look like for each of the options. Assume that May just started so we are just counting October through April, though there may be activity in May.

    Excel Result.JPG
    "0-Pending"=activity in current (partial month) but none in prior months
    "1-New"=activity last month(April) but none for the 5 months before that
    "2-Active 1"=activity last month(April) but not "New" classified, OR activity in March
    "3-Active 2"= no activity last 2 months (March/April) but has activity in 3rd or 4th month (Jan/Feb)
    "4-At Risk"=no activity last 4 months (Jan through April) but has activity in Nov or Dec
    "5- Available"=Activity in October, but none since

    Here is the Excel formula ("pending" is not listed in there, I use to pull it out manually):
    =IF(AND([@[April 2020]]>0,[@[March 2020]]=0,[@[February 2020]]=0,[@[January 2020]]=0,[@[December 2019]]=0,[@[November 2019]]=0),"1-New",IF([@[March 2020]]+[@[April 2020]]>0,"2-Active 1",IF([@[February 2020]]>0,"3-Active 2",IF(AND([@[February 2020]]+[@[March 2020]]+[@[April 2020]]=0,([@[January 2020]])>0),"3-Active 2",IF([@[December 2019]]+[@[November 2019]]>0,"4-At Risk","5-Available")))))

     

    How can I achieve this feild in Domo? The months will shift each month, so it cannot include the month names. I have attached a shot of my Domo so far:

    Thanks in advance

    Best Answer

    • Coach
      Answer ✓

      Hello,

      You should be able to accomplish this with a CASE statement in a Beast Mode. Try this as a starting point:

       

       

      1. case 
           when count(case when month(`Submitted`) = month(CURDATE()) and year(`Submitted`) = year(CURDATE()) then `Order $` end) > 0 and -- Orders in the current month
                count(case when month(`Submitted`) < month(CURDATE()) or year(`Submitted`) < year(`Submitted`) then `Order $` end) = 0    -- No orders in prior months
                then '0-Pending'
           when count(case when month(`Submitted`) = month(date_sub(CURDATE(),interval 1 month)) and year(`Submitted`) = year(date_sub(CURDATE(),interval 1 month)) then `Order $` end) > 0 and -- Orders in previous month
                count(case when month(`Submitted`) < month(date_sub(CURDATE(),interval 1 month)) or year(`Submitted`) < year(date_sub(CURDATE(),interval 1 month)) then `Order $` end) = 0      -- No orders before previous month
                then '1-New'
           when count(case when month(`Submitted`) = month(date_sub(CURDATE(),interval 1 month)) and year(`Submitted`) = year(date_sub(CURDATE(),interval 1 month)) then `Order $` end) > 0 or  -- Orders in previous month
                count(case when month(`Submitted`) = month(date_sub(CURDATE(),interval 2 month)) and year(`Submitted`) = year(date_sub(CURDATE(),interval 2 month)) then `Order $` end) > 0     -- Orders in month before last
                then '2-Active 1'
           when count(case when month(`Submitted`) = month(date_sub(CURDATE(),interval 3 month)) and year(`Submitted`) = year(date_sub(CURDATE(),interval 3 month)) then `Order $` end) > 0 or -- Orders 3 months ago
                count(case when month(`Submitted`) = month(date_sub(CURDATE(),interval 4 month)) and year(`Submitted`) = year(date_sub(CURDATE(),interval 4 month)) then `Order $` end) > 0    -- Orders 4 months ago
                then '3-Active 2'
           when count(case when month(`Submitted`) = month(date_sub(CURDATE(),interval 5 month)) and year(`Submitted`) = year(date_sub(CURDATE(),interval 5 month)) then `Order $` end) > 0 or -- Orders 5 months ago
                count(case when month(`Submitted`) = month(date_sub(CURDATE(),interval 6 month)) and year(`Submitted`) = year(date_sub(CURDATE(),interval 6 month)) then `Order $` end) > 0    -- Orders 6 months ago
                then '4-At Risk'
           when count(case when month(`Submitted`) = month(date_sub(CURDATE(),interval 7 month)) and year(`Submitted`) = year(date_sub(CURDATE(),interval 7 month)) then `Order $` end) > 0 -- Orders 7 months ago
                then '5-Available'
        end

       

       

       

    Answers

    • Coach
      Answer ✓

      Hello,

      You should be able to accomplish this with a CASE statement in a Beast Mode. Try this as a starting point:

       

       

      1. case 
           when count(case when month(`Submitted`) = month(CURDATE()) and year(`Submitted`) = year(CURDATE()) then `Order $` end) > 0 and -- Orders in the current month
                count(case when month(`Submitted`) < month(CURDATE()) or year(`Submitted`) < year(`Submitted`) then `Order $` end) = 0    -- No orders in prior months
                then '0-Pending'
           when count(case when month(`Submitted`) = month(date_sub(CURDATE(),interval 1 month)) and year(`Submitted`) = year(date_sub(CURDATE(),interval 1 month)) then `Order $` end) > 0 and -- Orders in previous month
                count(case when month(`Submitted`) < month(date_sub(CURDATE(),interval 1 month)) or year(`Submitted`) < year(date_sub(CURDATE(),interval 1 month)) then `Order $` end) = 0      -- No orders before previous month
                then '1-New'
           when count(case when month(`Submitted`) = month(date_sub(CURDATE(),interval 1 month)) and year(`Submitted`) = year(date_sub(CURDATE(),interval 1 month)) then `Order $` end) > 0 or  -- Orders in previous month
                count(case when month(`Submitted`) = month(date_sub(CURDATE(),interval 2 month)) and year(`Submitted`) = year(date_sub(CURDATE(),interval 2 month)) then `Order $` end) > 0     -- Orders in month before last
                then '2-Active 1'
           when count(case when month(`Submitted`) = month(date_sub(CURDATE(),interval 3 month)) and year(`Submitted`) = year(date_sub(CURDATE(),interval 3 month)) then `Order $` end) > 0 or -- Orders 3 months ago
                count(case when month(`Submitted`) = month(date_sub(CURDATE(),interval 4 month)) and year(`Submitted`) = year(date_sub(CURDATE(),interval 4 month)) then `Order $` end) > 0    -- Orders 4 months ago
                then '3-Active 2'
           when count(case when month(`Submitted`) = month(date_sub(CURDATE(),interval 5 month)) and year(`Submitted`) = year(date_sub(CURDATE(),interval 5 month)) then `Order $` end) > 0 or -- Orders 5 months ago
                count(case when month(`Submitted`) = month(date_sub(CURDATE(),interval 6 month)) and year(`Submitted`) = year(date_sub(CURDATE(),interval 6 month)) then `Order $` end) > 0    -- Orders 6 months ago
                then '4-At Risk'
           when count(case when month(`Submitted`) = month(date_sub(CURDATE(),interval 7 month)) and year(`Submitted`) = year(date_sub(CURDATE(),interval 7 month)) then `Order $` end) > 0 -- Orders 7 months ago
                then '5-Available'
        end

       

       

       

    • Member

      Thanks so much @MichelleH !. Sorry for the late reply. I had an error (my fault) and got too busy to dig in and see where it was going wrong.

      This worked great! Thanks for sharing your knowledge & time! ?

    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Quick Links

    Categories