I have a use case where I am currently doing some capacity planning and want to check a condition in the week that I want to plan this capacity in, and it then brings it into the correct week. It is working exactly as intended right now. Point being, there are tons of business cases, and if they were to change how it is currently working, then anyone who is using the current functionality would need to update their formulas. If they were to change how IF with POST works, then I would have to stage it just like you do now.
There are a ton of things in Anaplan that need to get staged for them to work for each individual business use case, and this is one of those cases. Here is an example of how you can quite simply make this work based on your user story.
As you can see, I check the condition in my source month, which then populates my IF line items which get posted.
I tend to be with you here that this is un-natural. I think what happens is that when the condition is false, then POST doesn't execute, and it seems Anaplan does not run this function period by period but as a whole.
You have run into a fundamental behaviour for Anaplan Calculation, I'll go into that a bit and then show a couple of solutions (one good, one not so good).
Background A formula is made by joining together functions, and for all but a few functions:
The evaluation of a function:
occurs at the target point*
without knowledge of the formula around.
Now, there are exceptions to point 1, but never to point 2. These exceptions occur with certain coordinate** transformations (offsets, post, currentversion, cumulate, … ), textlist, and rank functions - for these functions the first parameter, the value, can itself be an expression and this expression is evaluated at a different point.
In case of offsets, post, and cumulate, this moving of the point of evaluation occurs along a specific dimension as a coordinate shift. Let's work through theformula:
IF Condition THEN POST(Source Value, Distance In Periods) ELSE 99
at point (May 20) the condition is FALSE, so return 99
at point (Feb 20) the condition is TRUE, so return POST(Value, Offset) - this will return the sum of all values that have an offset targeting Feb 20. Note that this will include all values, regardless of whether the condition is true or not at the source point.
Solution Now, with all that background, let's get to the solution. I believe that you only want to only post the value if the condition is true, and if the condition is not true we want to add the fill value.
Jason nearly had this. The missing piece is that the first and second parameters to post can be expressions, and these expressions will be evaluated for all Time coordinates. It is at this point that values that have the correct offset amount are 'posted' to the 'target' cell.
I have two slightly different solutions, first:
POST(IF Condition THEN Source Value ELSE 99, IF Condition THEN Distance In Periods ELSE 0)
This is essentially moving Jason's logic inside the POST. I wouldn't do this though, as this is more computationally heavy than the following:
IF Condition THEN POST(IF Condition THEN Source Value ELSE 0, Distance In Periods) ELSE POST(IF Condition THEN Source Value ELSE 0, Distance In Periods) + 99
This formula is saying 'if the condition is true for the current point then get any values that are posted to it, and otherwise get values that are posted to the current point and add 99. This could be rewritten as follows by moving the repeated POST out of the IF
POST(IF Condition THEN Source Value ELSE 0, Distance In Periods) + IF Condition THEN 99 ELSE 0
This is easier to understand, but slightly slower computationally (we always do an addition, rather than only doing it if necessary).
Conclusion There is no bug/issue with POST, IF or any combination of POST and IF. The solution to the problem requires an understanding of how a formula f is evaluated at any given point p, and also of how special functions like POST allow you to reference source data and even calculate at a point other than p.
Terminology * Point: the position of the cell that is being evaluated, examples:
(Jan 20, Actual, Product X)
** Coordinate: the position in a given axis of a point, e.g. the Time coordinates for the points above are Jan 20, May 20.
Thank you very much for the detailed and deep analysis you provided. I believe the new piece of information I got from your post - no pun intended 😊 - is that the POST() function - along with a few similar ones - calculate at a different point than the rest of the of functions or as we expect. My question is why? --- Yes I'm like a 3 years old who keeps asking why until receiving a satisfying answer 😂
I'll dig deeper into this a bit to learn more, and I would appreciate your help if I have questions. the first being how did you come across this information?
I really appreciate everybody's participation in the discussion, it was most helpful
Update from L3
I shared my question with Anaplan Support and they escalated the issue to L3. I received the L3 response yesterday which stated "I have received a response from the Development team and they have determined that this is not a bug with the platform and the behaviour you have observed is working as designed. In your example, as the condition is not met in the May 20 cell, the formula will return 99 for that cell which overrides the POST coming from the Feb 20".
They didn't provide any more explanation. Thanks @jasonblinn for your examples.
I wasn't really looking for workarounds as much as being very curious as to why POST() is not doing what is expected at first glance.
I believe if the part about the way the function(s) calculates is explained in Anapedia, it would provide more clarity.
Again, I really appreciate the discussion and the participation
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 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)
- 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!