Multi Row Formula

[mehu75]

Hello all.

Please see attached. Outcome is as desired.

If the Ef amount is >0 and no other entry for the same account number then 1

if Ef mount <0 and no other entry for the same account then 0

if sum ef amount =0 then +1 for +ve number and -1 for the -ve number

if sum ef amount <>0 then +1 for +ve number and 0 for the -ve number

I used Multirow formula and bunch of either weird things to get to the result(column name=New Field), but when you look at the last Policy # ( J) it is should show -1 and +1 for ef amount =-49514 and 49514 and +1 for  24757, but it is not doing so. any help will be appricated. I would like to see if someone can show me how to solve all these in alteryx, I created like a union join on the same table one for +ve number and 1 for -ve number.

[Christina_H]

This took me a couple of attempts to see what you were trying to do.  I've left my first attempt in the attached so you can see how I initially interpreted it if you're interested!

I think the reason you're not getting the result you want for policy J is that the matching value positive and negative rows aren't consecutive.  Rather than a multi-row formula, I've used tile tools to match up values that should show +1/-1 (I think) then assigned all the values in a single formula.  The tile tools are important if there are repeating values.