# Nested IF AND for Columns in a Pivot Table

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. "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:

• 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`