Alteryx Analytics Hub

Find answers, ask questions, and share expertise about Alteryx Analytics Hub.
SOLVED

Filter product data based on the numeric sign of subtotals

sureslala
6 - Meteoroid

Hi 

I need to filter my data based on the numeric signage of product sub totals. I have various energy products (Brent, Brent Dated, Fuel Oil etc.) with contract dates and positions.

 

As an example, if the overall positions for Brent is negative, then I want to group all the Brent contracts with negative position signs as 'True' and all the positive position signs as 'False'. If the sub total of product Fuel Oil is positive, then I want to group all Fuel Oil contracts with a positive position sign as 'True' and all the contracts with negative positions as 'False' etc.

 

I have all my data in a formula but cannot create a generic formula to separate my data into 2 groups, refer attachment where I have created sample numbers.

 

All the individual Brent contracts add up to negative (6,946,621.20) so I want to group each individual contract with a negative sign in one group (I have highlighted them in red) and all the positive signed contracts in another group. I need to do this for every product, I have 14 different products.

 

Thanks for your assistance.

Sures

 

4 REPLIES 4
BrianR
Alteryx
Alteryx

Hi @sureslala - take a look - I think I have captured the logic you are looking for. I mocked up some data based on your sample. In one case I used the Brent example where the total is negative - in that case, the workflow filters those records into one bucket, while all other contracts go into another bucket. I added in another example where the total is positive, and the inverse is true.

 

Brian

 

PS - the Designer Discussions group is a good one to post to in the future, that's where a lot of posts get answered.

NicoleJohnson
14 - Magnetar
14 - Magnetar

Try something like the attached!

  • Filter for your totals, and then use a formula to create a Boolean (true/false) field that will tell you if the total is negative or positive
  • Join this NegativeIndicator back to your non-total fields by Product
  • Use another formula to check if the total indicator is negative and the position value is negative, or if both are positive. If that's the case, the second formula will be true (for your grouping) otherwise it will be false if the signage is mismatched.

NicoleJohnson_0-1640018189805.png

 

Hope that helps! Cheers,

NJ

sureslala
6 - Meteoroid

Thanks Nicole
Your workflow works perfectly and resolves my data filtering problem. Your answer is very clear, logical and easy to follow. Thank you again, its very much appreciated!

Cheers

Sures

sureslala
6 - Meteoroid

Thanks Brian for taking the time to examine my problem. I could make this solution work however it would generate potentially 16 different product databases to work through (# of products I have), adding more complexity to my workflow. I've received a workflow that produces the desired results in a simpler format which I have managed to incorporate into my workflow. Thanks again for your time and for assisting me with my query.

 

Regards,

Sures 

Labels