How to reference an InDB formula in another InDB formula

[csh8428]

I have several InDB formulas for MS SQL 

[PREV_STATUS_c] 

LAG(MTN_STATUS_IND) OVER (PARTITION BY CUST_ID, CUST_LINE_SEQ_ID ORDER BY EXP_DT)

[PREV_EXP_DT_c]

LAG(MTN_STATUS_IND) OVER (PARTITION BY CUST_ID, CUST_LINE_SEQ_ID ORDER BY EXP_DT)

I have another formula that references the 2 formulas above, but I can't get it to work. Since it's InDB the error isn't helpful

[IS_NEW_GRP_c]

CASE
WHEN MTN_STATUS_IND <> PREV_STATUS_c OR EFF_DT <> (PREV_EXP_DT_c + INTERVAL '1' DAY)
THEN 1
ELSE 0
END

Any ideas why?

[apathetichell]

assuming formula 3 is after formula 1 and 2 -> see if alteryx expects fields in quotes (try putting a field in formula tool) - try running to refresh field metadata. 

short is there is no reason you cannot reference formula in-db created fields in another formula in-db -> albeit I can see being effected by if you have the ability to create temp tables on the DB on some systems.

[csh8428 · Accepted solution]

if figured it out, Alteryx(or our sql server) didn't like the date addition syntax. I changed it to the DATEADD function and it worked.

CASE
WHEN MTN_STATUS_IND <> PREV_STATUS_c OR EFF_DT <> DATEADD(dd,1,PREV_EXP_DT_c)
THEN 1
ELSE 0
END