Hi, I want this formula to pick up on Charge type and State and if the following parameters are met to SUM everything after the word THEN. Can anyone help with correcting formula
Did you add and ELSE and ENDIF to the end of your formula? The way its written, its not complete. Also you have it set to V_WString. Should it be Double instead?
Yes, not sure what I'm doing wrong here - ENDIF doesn't work either
What is after your ELSE? It should be something like ELSE 0 ENDIF. If its still not working, make sure all of the fields you are adding together are all DOUBLE before the formula.
@Khristian_Evans I dont think your OR condition is correct in that formula, you can replace or with IN operator like below
IF [CHARGE_TYPE] IN ('Early Termination Fee', 'Refund Adjustmnet','Account Change Charge', 'Account Change Charge_TE' , 'Late Fee' , 'Past Due Interest' , 'Past Due Interest_PA_1.5' , 'Energy ADJUSTMENT' AND [State] = 'PA'THEN[USAGE_CHARGE_AMT] + [LINE_LOSS_CHARGE_AMT] + [OTHER_CHARGE_AMT] + [DEFERRED_AMT] + [BUDGET_CHARGE_AMT] + [GRT_TAXES]
@Khristian_Evans - watch the videos - they'll help identify this kind of thing. Alteryx if statements follow an:
IF
THEN
ELSE
ENDIF logic. They need at least those four parts. You should figure out what you want in the null case (null would be fine) - but you need to have something in the ELSE statement.
@Khristian_Evans
IF [CHARGE_TYPE] IN ('Early Termination Fee', 'Refund Adjustment', 'Account Change Charge', 'Account Change Charge_TE', 'Late Fee', 'Past Due Interest', 'Past Due Interest_PA_1.5', 'Energy ADJUSTMENT') AND [State] = 'PA' THEN [USAGE_CHARGE_AMT] + [LINE_LOSS_CHARGE_AMT] + [OTHER_CHARGE_AMT] + [DEFERRED_AMT] + [BUDGET_CHARGE_AMT] + [GRT_TAXES] ELSE 0 ENDIF
Thank you that solved issue, now I have another unfortunately. The values are outputting for "PA" state but any other state is returning null value would you know how to fix this? I would this the last part of formula after ELSE would include anything not "PA"
For the other states are there any Null values in the AMT fields? You might want to data cleanse all of your AMT and TAX fields to make sure there are no null values in any of them. If there are and there should be you'll need to add some conditions on how to handle the nulls in the fields.
While @cjaneczko has worked dilligently to try to help you here - your best usage of time is to WATCH THE TRAINING VIDEOS. That will help you identify what you need to build your formula and how Alteryx handles things like nulls.
