Ability to sum based on boolean

Ability to sum based on boolean

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.

The content in this article has not been evaluated for all Anaplan implementations and may not be recommended for your specific situation.
Please consult your internal administrators prior to applying any of the ideas or steps in this article.
5 Comments
Previous Contributor
 
Status changed to: Considered for Future Roadmap
Certified Master Anaplanner

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'.

Super Contributor

Guido.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.

Certified Master Anaplanner

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.

Super Contributor

@Guido.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