Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

Allocating one row across columns

Highlighted
Atom

Hi, 

 

I have created a workflow that lists company revenue by state with a total row for each company. I am looking to allocate any positive values across the entire column.

 

As an example - for Company A in the attached output, I would like to allocate the $9.3M in New York across all states. I tried using the multi-row formula, however, that did not allow me to create a formula that will update rows on condition that the value is positive.

 

Is there a different tool I can use to accomplish this? Any ideas would be helpful!

 

Thanks!

Highlighted
Quasar
Quasar

Hi @BHersh,

 

I did a sample workflow, a multi row formula does the trick, you have to sort first and then use the multi row formula, you can add a condition in the formula like

 

if companyA >0 and ...

 

it should work from what i understood.

 

Hope it helped!

Highlighted
Castor
Castor

Hi @BHersh 

 

Here's a workflow that allocates the positive revenue to the other states.

 

wf.png

 

Start by splitting into positive and other state.  Count the number of non-positive records.  Use this to determine the amount of revenue to allocate to each state.  sum all the totals and the add this to each non-positive state.  I wasn't sure how you wanted to allocate the data, so I just used an equal amount to each non-positive one.

 

Results in the Total column below

 

Results.png

 

Dan

Highlighted
Atom

Thank you @danilang! This works to allocate the values equally across all the states. 

Do you have any suggestions on how to separate the positive and negative values if there are multiple columns? In my sample data, I only presented one company column, but I'm hoping there is a way to apply a filter when there are multiple columns.

Highlighted
Castor
Castor

Hi @BHersh 

 

Can you provide some sample data?  

 

Dan

Highlighted
Quasar
Quasar

@BHersh Hi,

 

have you looked at what I did as a sample, with this method, you could easily have the data in one column with a transpose tool!

Labels