This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
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.
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.
PS - the Designer Discussions group is a good one to post to in the future, that's where a lot of posts get answered.
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.
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.