Sorting then summing down a list
Options
Hi, I have a list of deals which are imports from salesforce opportunities which I need two things done to. Firstly i need them sorted by the close date and secondly i need a cumulative sum down the list so that i can tell what percentage of quota has been attained. Anyone know how to 1. sort the list based on a line item, and 2. do a cumulative sum based on the previous amounts?
| Deal | Close Date | Amount | Cumulative Sum |
| 1 | 6/11/15 | 10,000 | 10,000 |
| 2 | 5/1/15 | 10,000 | 20,000 |
| 3 | 1/1/15 | 10,000 | 30,000 |
| 4 | 3/1/15 | 10,000 | 40,000 |
| Howard | 40,000 | 40,000 |
Cheers, H
Tagged:
1
Best Answer
-
Jack
An intreguing way to overcome the problem, however there are 2 things to be aware of if you were to use this approach:
1) The di01 and di02 lists have to use ascending numerical naming for their contents, this would not work if they were "Product01" ... "Product99"
2) As you indicate in your opening comments, the Triangle module is N^2, which could make it very large and sparse (imagine performing this with Lists of 100,000 products)
We're hoping that newly developed functions to perform accumulation in this way will be ready for release soon.0
Answers
-
The sorting I figured out. Not hard was just doing it incorrectly. Anaplan told me that summing down the list isn't possible yet until August release. Anyone have a work around?
Cheers,
H0 -
I have a method but it requires a number of cells on the order of N^2 where N is the number of products and requires maintaining a dummy version of the products list.
Define Lists: di01, di02
di01 represents your original list and di02 represents a clone of di01.
Elements: 1, 2, 3, 4, 5, 6, 7, 8, 9, 10
Lists need not have a Top Level Item.
Define List: OneState
Element: 'State'
List need not have a Top Level Item
Define Module: Data
Applies to: di01, OneState
Define Line Item: Data
Data Type: Number
This contains the data you wish to sum along di01.
Define Line Item: Cumulative Sum
Data Type: Number
Formula: Triangle.Data[SUM: Triangle.Cumulative]
Input the formula for this after the definition of the following module.
Define Module: di02 => di01
Applies to: di02
Define Line Item: Reference
Data Type: di01
Formula: FINDITEM('di01', NAME(ITEM('di02')))
Define Module: Triangle
Applies to di01, di02, OneState
Define Line Item: di02 Transfer
Applies To: di02, OneState
Data Type: Number
Formula: Data.Value[LOOKUP: 'di02 => di01'.Reference]
Define Line Item: Data
Data Type: Number
Formula: 'di02 Transfer'
Define Line Item: Cumulative
Data Type: OneState
Formula: IF VALUE(NAME(ITEM('di01'))) >= VALUE(NAME(ITEM('di02'))) THEN OneState.State ELSE BLANK
The use of the OneState dimension as a root dimension in all aggregation and calculation modules provides a lot of flexibility since you can SUM, AVERAGE, MIN, MAX and other things without the use of summary items and the SELECT function.0 -
Hi Duncan.
1: My use of numerical names for the list was an arbitrary choice. You can just as easily have a module that, by using RANK for instance against the list in question in order to produce an ordered list. At this point you replace the Triangle formula with an appropriate inequality check against line items.
2: Yes, there is no overcoming the N^2 fact as of yet. However, the determination of applicability is a function of what other modules in your model dominate the model size. It's rare for a particularly useful model to have fewer than two dimensions (maybe even three) in all modules so taking an N^2 hit doesn't seem like too much of a problem except in really sick cases.
Additionally, what is the standard practice these days for taking a SUM, AVERAGE, MIN or MAX down an entire list as aggregation? I've only seen references to X[SELECT: List.TopLevelItem] and Summary Types on forums but the general method of using OneState to root your modules as defined above is somewhat more efficient and much easier to implement.0 -
Jack
1. True, but this would add some additional complexity to the formulae required to perform the triangle comparison
2. Also true, if every module within a model has 2 or fewer dimensions it would be an unusual planning solution (however could be akin to an MDM Hub model). However, a 2D module of 100,000^2 is still quite sizable, and 400x bigger than a 4D module with 12*1,000*500*4 (periods/customers/products/versions) in it. A good modeler should aim to balance the optimisation of size, performance and usability
I am not the right person to recommend standard practices, perhaps someone from Customer Success can assist on this.
I hope you'll like the new functions we are developing.1 -
Duncan,
1. Sure, but that's what formulas are for and that's why we like doing what we do!
2. I wonder if a decent model could still be built from that nasty edge case set of parameters.
I'm looking forward to the new functions. I think it's going to render obsolete some of my practices but I wonder what funky new things I'll be able to concoct.
I'm a massive stickler for sparsity/unnecessarily generated cells, so I do all I can to reduce it as much as possible in densely designed modules and use N^2 tricks when the numbers allow. Luckily I've yet to reach a spot where it would be an issue.
Is there a page anywhere discussing the specifics of the new functions?
Cheers for the feedback.0 -
Jack
I don't think there is currently a publicly visible page which discusses this function (or any other that are "in development" or "being considered"). As you will appreciate, Anaplan make all efforts to ensure that new features are helpful and of a high quality before being released, so we like to be sure things make the grade before being announced.
This said, colleagues in product development and Customer Success often frequent these forum pages, and posts containing potential enhancements are noted. When we're gathering the requirements for a particular new feature, we often reach out to customers, partners and internal colleagues to ensure we get a broad range of ideas and feedback. If you would be interested in participating in future, please contact Support and they'll make sure your request is passed to the correct person.0