Summarizing only Negative numbers and Positive Numbers in separate Line Items

Hello,

I am trying to solve a dilemma. I have values for a list property called "adjustments". These values can be positive or negative and there can be more than one in the same time period for the same product. (eg: Apples in December 2020 had one adjustment for -10 and another adjustment for 20. The Net effect of these adjustments is 10, but I am trying to identify the -10 in a line item called Negative Adjustments and the 20 in a separate line item called Positive Adjustments.

The point I am trying to prove with the data is that there may be situations where we are adjusting multiple times within the same time frame and those adjustments are potentially offsetting each other which then makes you question whether the adjustment is necessary in the first place.

Does anyone have a solution that would help me sum only the negative numbers within a list property?

I appreciate any help out there.

Accepted answers

charlotte.chiasson

Thank you very much Kevin. The second solution you suggested is similar to another method I have used for separating text items in a list and using that to lookup only the relevant text field. I never thought to use that same method for numerical values. I will give it a try and see what comes of it.

I appreciate your prompt response. 🙂

All comments

kevin.cho

Two similar, but slightly different, methods.

My assumption here (based on your post) is that there are two lists (Products and Adjustments) and two modules. In the below solution Adjustments has a top level item called "Totals" (just makes the summary exist, and means we don't have to do any other aggregations later).

Module 1 - dimensionality = Products, Time, Adjustments

Line Items - "Adjustment Value" (input)

Module 2 - dimensionality = Products, Time

Line Items - Negative Adjustment Value, Positive Adjustment Value, Net Adjustment

Option 1:

Might seem obvious, but create two new line items against Module 1 - "Negative Adjustment Value" and "Positive Adjustment Value", which returns the appropriate adjustment value.

Negative Adjustment Value = MIN(0, Adjustment Value)

Positive Adjustment Value = MAX(0, Adjustment Value)

In Module 2:

Negative Adjustment Value = 'Module 1'.Negative Adjustment Value

Positive Adjustment Value = 'Module 1'.Positive Adjustment Value

In this solution, we're summing all entries regardless, but have modified the values such to return 0 if not of the appropriate type (negative/positive respectively).

Option 2:

Similar, but slightly different option. Two new line items called "Negative Adjustment Helper" and "Positive Adjustment Helper" in Module 1 - format as "Products".

Negative Adjustment Helper = IF Adjustment value < 0 THEN ITEM(Products) ELSE BLANK

Positive Adjustment Helper = IF Adjustment Value > 0 THEN ITEM(Products) ELSE BLANK

In Module 2

Negative Adjustment Value = 'Module 1'.Adjustment Value[SUM: Negative Adjustment Helper]

Positive Adjustment Value = 'Module 1'.Adjustment Value[SUM: Positive Adjustment Helper]

In this solution however, we're only aggregating the values that are relevant - we limit this by creating those aggregation helpers ("XX Adjustment Helper").

Let me know if you need further explanation on either of these 😊

charlotte.chiasson

I appreciate your prompt response. 🙂

kevin.cho

No worries, glad to help!

MirBorishkevich

Hey @charlotte.chiasson You can always use the SIGN formula to determine if a number is positive or negative or if its a zero. You can use that one line item as a filter to filter down just to the positives, negatives, or zeros.

See anaplan technical documentation below:

https://help.anaplan.com/8f82d325-4508-4844-bf3b-c379d9f02107-SIGN

Quick Links

Can you help?

Unanswered questions

Excel date format for February 2030, 2031 and so forth don't align with Anaplan format
Hello, I’m running into a recurring issue when importing CSV files into Anaplan, specifically with dates in February for the years 2028, 2029, and beyond. Here’s what’s happening: In Excel, the date format is set to Custom (e.g., 2/26/2025). When imported into Anaplan, the date is interpreted as 26-Feb, where: Month = Feb…
Introducing Anaplan API Python SDK
Hey everyone, I wanted to share the Anaplan Python SDK with you. It's a Python Library that wraps all Anaplan APIs and makes it easier to interact with Anaplan programmatically. It is mostly designed with Data Integration Scenarios in mind, but it does support all APIs including ALM, SCIM, Audit, and Cloud Works. Please…
Cloudworks Big Query Integration Fail
Hello, I am receiving an 'Anaplan Upload Failed' Status Description when testing my integration with a Big Query dataset. The integration imports data from BQ to our Anaplan model. No other details given in the error log. I suspect that Cloudworks is not even picking up the file but am not sure what we did wrong on the set…