Why is a good question. I wouldn't say that functions calculate in an unexpected way, rather there are different categories of functions that provide different capabilities to the modeller. It comes down to wow Anaplan formulas work, so let's consider something basic and familiar. Given: List X with items x1, x2, x3, and top level xTotal Line Item A, dimensioned by X and Quarters, values: | | Q1 | Q2 | Q3 | Q4 |
| x1 | 1 | 2 | 3 | 4 |
| x2 | 1 | 1 | 2 | 3 |
| x3 | 1 | 2 | 4 | 8 |
| xTotal | 3 | 5 | 9 | 15 | Line item B, dimensioned by Quarters, formula = A * ( 9 + 1 ) Then the values of B are: | Q1 | Q2 | Q3 | Q4 |
| 30 | 50 | 90 | 150 | Why? Well we work through the calculation step by step. First we're doing a multiplication. All that multiplication does is multiply two numbers, it doesn't need to know where they come from! Next we need to get those two input numbers to multiply. The first number comes from a line item reference, and the second is given by adding 9 and 1, that is 10. The line item reference uses a few simple rules - we match coordinates for all axes where possible, and if a dimension is missing then we use the top level. So in this case when we evaluate the formula at point (Q1) we get the value from A at (Q1, xTotal), which is 3. This is obviously very different to an Excel way of operating where you provide a different formula cell by cell. Most functions work in this manner - they don't need to know where the values come from - we can call these 'Value' functions. These include Logic, Arithmetic, Call Centre functions, and more... Now sometimes we don't want to match all the coordinates, for various reasons. That is where the other classes of functions come in: Expression Allowed Expression not allowed Cumulate, Decumulate, HalfYearToDate, MonthToDate, QuarterToDate, WeekToDate, YearToDate - cumulate over a given axis (time) Collect - a bit like a line item reference but adding the line item itself to a dimension of the result, the standard reference rules still apply though Lag, Lead, Next, NextVersion, Offset, Previous, PreviousVersion - offset coordinate on a given axis (time or versions), all other coordinates of the point are unchanged HalfYearValue, MonthValue, QuarterValue, WeekValue, YearValue - get source value at a given ancestor level (time) Profile, Post, Spread - sum up values on a given axis (time) where the source values are at an offset coordinate at which the defined offset amount returns the result coordinate (not quite a 'push', as we've seen) MovingSum, TimeSum - sum up values over a specified time range IsFirstOccurrence - first occurrence of value on a given axis (a list) Sum, Min, Average, FirstNonBlank, Textlist, ... - dimensional transformation - collect the points of the source for which the relation value is the coordinate for a specific dimension in the target point, then combine the values of the source at these points to produce a single value (e.g. combine using addition for Sum) Textlist - concatenate expression over an axis (a list) Select, Lookup - dimensional transformation - pick a point in the source by specifying a coordinate for a specific dimension, either by a static selection (SELECT) or by using the lookup relation value at the target point (LOOKUP), then return the value of the source at this point. Rank, RankCumulate - rank/cumulate values over a block (Version + Time coordinates are fixed) These functions allow us to take in data from points other than that which direct line item reference would give us, to consider previous data, or a total dataset, or map data from one space to another. Personally I find this is where the fun kicks in! To go into POST behaviour in more detail: POST(valueExpr, offsetExpr) evaluated at point p: The point p has a time coordinate, p(Time). Find all other points q, with time coordinate q(Time), and for all non-time axes q = p, where the offset on the time axis between q(Time) and p(Time) is equal to the evaluation of offsetExpr at q. Evaluate valueExpr at all these q, and sum them up. You can see in this manner of expressing, it isn't that the values are pushed, so much as fetched when they match the condition such that they would push. Subtly different! That got a little technical in terminology again, sorry, it's hard to describe the precise behaviour without getting technical 🙂 As to how I got this information - a lot of experimentation over a long time! Dan
... View more