Sorting then summing down a list

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

Accepted answers

PrevContributor

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.

All comments

PrevContributor

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,
H

PrevContributor

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.

PrevContributor

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.

PrevContributor

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.

PrevContributor

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.

PrevContributor

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.

Quick Links

Can you help?

Unanswered questions

Azure blob connection Cloudworks vs ADO
I am trying to connect to Azure blob, the credentials work on cloudworks, but do not work on ADO. Has anyone else experienced this issue?
Berapa nomor Tokopedia?
Tokopedia Menyediakan layanan WhatsApp? Untuk menghubungi layanan Tokopedia melalui (WA)62_08174_9080_16 kamu bisa mengikuti.
Looking for Anaplan End-Users to take a short survey!
We are looking for Anaplan end-users to provide feedback on their experiences with the Excel add-in. Interested individuals will respond to this 5-minute survey to help us understand personal needs and behavior when using the add-in. The feedback provided by survey takers is essential to the roadmap of Anaplan's products.…