Nested IF AND for Columns in a Pivot Table

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

  • MichelleH
    MichelleH Coach
    Answer ✓

    Hello,

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

     

     

    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

  • MichelleH
    MichelleH Coach
    Answer ✓

    Hello,

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

     

     

    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

     

     

     

  • AJ2020
    AJ2020 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! ?

Categories