Dynamic Formula to Set Total to Zero When Any Current Field is Zero

[KamenRider]

Hello Alteryx Community,

I'm working on a workflow where I need to dynamically set the Total field to zero if any of the current_field values are zero. I have multiple Count_XX_XX_XXXX fields, and I want to apply this logic across all of them without hardcoding each field name.

Currently, I'm using a Multi-Field Formula tool to set individual fields to zero based on a condition, but I'm struggling to aggregate this logic to affect the Total field. Here's what I've tried so far:

Multi-Field Formula Tool: Applied a conditional formula to each current_field to set it to zero if the Reject Reason is "0".

Summarize Tool: Attempted to aggregate flags indicating zero values, but I'm unsure how to use this to set the Total field.

See screenshot below for your reference.

 

Could anyone provide sample workflow on how to dynamically set the Total field to zero if any current_field is zero? Any tips or best practices for achieving this in Alteryx would be greatly appreciated!

Thank you in advance for your help!

Kamen

[atcodedog05]

Hi @KamenRider,

Can you provide some sample data and expected output.

[binuacs]

@KamenRider something like below?

[apathetichell]

I think the easiest way might be creating a new column by multi-field formula. using a boolean test if the value of [_CurrentField_] is 0 -- and then following the transpose/summarize for max value rule.

[KamenRider]

Hi @binuacs @apathetichell @atcodedog05 

I created a dummy file where you can use as an input file. Please see attached. Final Output should look like this. Total should be correct.

 

Please let me know if you have questions.

Thanks,

Kamen

[atcodedog05]

Hi @KamenRider,

Here is a solution.

Hope this helps : )

[KamenRider]

Hi @atcodedog05 

Thanks for the response. I made some update as per attached. Is it possible for you to combine those with same NII # or date?

 

Correct

 

Looking forward for your response.

Thanks,

Kamen

[atcodedog05 · Accepted solution]

Hi @KamenRider,

Please find the updated solution. I did some sorting changes too.

Hope this helps : )

[KamenRider]

Hi @atcodedog05 

Thank you so much. You nailed it!

Kamen