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.
Jason
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.
Hey @nathan_rudman
that makes sense.
I know we are able to find workaround but I can’t justify the way it works as “normal”.
That’s the difference.
I think @jasonblinn and @Misbah have given explanations of this previously.
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)
@daniel_harman Class!
How on earth did I miss POST(IF..........). Thank you for the deeper analysis of this issue.
Hi @daniel_harman
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
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:
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