Ability to sum based on boolean

AnaplanIdeas

Description of enhancement required
Ability to allow to aggregate using Boolean mapping. Currently mapping can only be applied over List elements. It would simplify many formulas if we could simply SUM/PRODUCT over TRUE/FALSE Boolean values.

 

An example of enhancement - See example below

*List called "Client" with 3 items. Boolean property: “Client.Active” with following values:
TRUE
FALSE
TRUE

• * Module A with Client dimension has LineItem called "Data" with following data:
  1
  2
  3

• Module B with no dimensions. Formulas in module B that could be very useful:
  B.Data=A.Data [SUMTRUE: Client.Active] = 4
  B.Data=A.Data [SUMFALSE: Client.Active] = 2
  B.Data=A.Data [PRODUCTTRUE: Client.Active] = 3
  B.Data=A.Data [PRODUCTFALSE: Client.Active] = 2

A story for why the enhancement is required, (how would it help the business process):
As mentioned, it would simplify many formulas if we could simply SUM/PRODUCT over TRUE/FALSE Boolean values.

AnaplanIdeas

Status changed to: In Review

Guido.kaandorp

The workaround we are currently using is to add a list YN with Yes and No as values. Then instead of summing over the BOOLEAN true, we sum over 'Yes'.

Kavadera

****.kaandorp, what you propose is not great, since it implies SUM/LOOKUP combination which is known to affect the overall performance. In order to workaround the boolean summing the best way we came up with is to create a separate list consisting out of single element. When you need a SUM you just create a line item that has an additional dimension of this special list, and thus you can overcome SUM/LOOKUP performance issue.

Guido.kaandorp

Hi @Kavadera, the reason for using this SUM/LOOKUP is size constrains. We have a big model where often we only require a total sum on one single dimension. Adding sum as summary method to the line item will cause to much model grows as it sums over all the used dimensions. From a size perspective it is the optimal solution. Let's us hope on a native solution coming soon which will be optimal on performance and size perspective both!

 

For smaller models you could indeed use a different approach as you mention. With creating a separate list you also require a process to populate/update this extra list. As alternative you could add an additional lineitem instead with a formula like "If 'condition=TRUE' then 'original value' else 0" and sum over this one. This approach will not require an additional process step. However this will not solve the PRODUCT function added by @AnaplanIdeas.

Kavadera

@****.kaandorp , I am afraid, you didn't understand me. I am also talking about big models, and of course I understand that the more dimensions a line item has, the more space it's summary requires. I was only saying that SUM/LOOKUP is bad practice that should be avoided at any cost.

 

And by the way, this idea along with the description was actually written by me long time ago. AnaplanIdeas is just a depersonalized account that was used to upload RFEs when launching this part of the community 🙂

david.savarin

I second this idea.

Have currently a use case where summing up on the go with booleans would be really a great additions to increase modelling flexibility.

For now i will be using multiple line item with if conditions

SudhirY

Hi

 

I'm looking for this functionality as well, what are the alternate ways to get the sum based on Boolean, please?

 

regards

Sudhir

Kavadera

@SudhirY the only way I know is to create a false Boolean list consisting of a single item and make  calculation on that false Boolean dimension.

NoahJ

I would greatly appreciate this functionality - I very often find myself having to create a 'Value - filtered' sort of line item that is simply IF <bool> THEN <value> ELSE 0 for the purpose of summing, being able to instead write <value>[SUMTRUE:<bool>] would remove the need for that, and allow me to be more efficient with the model size.

julia.sung

This would be a great additional functionality. Currently have to create an extra line item in a module that is already very large just to do the summing.

kosterhout

Just to keep this on Anaplan's radar, this is still needed in 2023. [SUMTRUE:] and [SUMFALSE:] would be huge additions. Some of our modules for our clients are several gigabytes and adding a single line for summing can add hundreds of millions of cells.

xin

Agree. It will simplify the calculation if we can use one-step sum if true, especially when the model is big and slow already