Pick list item based on property and use it as a dimension/LOOKUP
Hi,
I have a list "Aging Buckets" with list items like below, and 2 properties (Range Min, Range Max):
Range Min (Property1) || Range Max (Property 2)
Bucket 0 0 1
Bucket 0_30 1 30
Bucket 30_60 31 60
Bucket 60_90 61 90
Bucket 90_180 ... ....
Bucket 180_360 181 365
There is an "Average" module which is dimensioned ONLY by the above list and has a line item: "Average Rate". This is all INPUT data that I have imported.
So the module looks like this:
Bucket 0 || Bucket0_30 || Bucket 30_60 || Bucket 60_90 || ... || ...
Average Rate 0.5% || 0.3% || 0.2% || 0.1% || ...
Finally, I have a calculation module called "Curve" where I wish to use the above info:
Assume, Curve Module has the following line items:
Aging Day (Ranges from 0 - 365) -- This is formatted as number and represents the "day" being tracked.
Extrapolated Rate -- I want the formula for this based on the below logic:
Extrapolated Rate should be the average rate from the "Average" module where it picks the appropriate bucket and the average rate. i.e.,
if Aging Day BETWEEN Range Min and Range Max
THEN then Average Rate of the appropriate bucket
Example, if Aging Day = 31, then Extrapolated Rate = Avg Rate[SELECT: Bucket30_60] and so on.
Is this possible or should I construct the list /modules in a different way?
Best Answer
-
Hi Visivasa,
Please refer to the screen shots below, and see if you can build something similar.
Please note the summary method of Curve.Aging Bucket Calc.
Thanks,
LipChean
5
Answers
-
Hi,
I think the previous solution works great (the recommendation is to put min and max values into a flat module rather than list properties).
Another alternative would be to have a flat module with list of days (0-365) as a dimension and a bucket value assigned for each item. That way you could lookup the bucket based on day value and rate based on bucket.
You wrote that input will be as number. In this case day value needs to be converted to list item with finditem function and that result could be used for bucket lookup (you'd also need text-function to convert number to text for finditem but my understanding is that text functions should be avoided for performance) This option assumes that inputted number can be found from the day list (is integer).
If day value is always between 0-365 and integer, could you consider using list of days as input? You could use that directly for lookup and avoid if statements and text functions (and also prevent user from inputting invalid figures, for example -999).
Br,
Jaakko
1 -
Hi,
To your point of "I think the previous solution works great (the recommendation is to put min and max values into a flat module rather than list properties).", i agree that the min and max properties should be min and max line items instead in a module. This is in alignment with the 'S' part of DISCO.
However i don't quite agree with the creation of 'Days' list for 2 reasons:
1. If the Aging Bucket changes min, max every year, this means the module must have 'Time' dimension, and someone needs to copy and paste 'Aging Bucket' as an entry from Min day to Max day carefully every year. Entering 2 numbers per Aging Bucket reduces possibility of errors.
2. What if the Aging Bucket goes beyond 365 days, eg in industry that looks at Account Receivables beyond 3 years, that translates to 365 * 3 = 1095 members. And together with point 1, this further increases the chance of an error.
Having said that, this highlights the power of Anaplan. There's many ways/ solutions for a problem.
Thanks,
LipChean
0 -
Hi, thanks for the points. I agree that with Anaplan's versatility different solutions can handle the job and the chosen solution (if time dimension is required or if aging days really are 0-365 etc.) should depend on the use case / model at hand.
0 -
Hi @Jaakko and @LipChean_Soh
Thanks so much for your involvement and solutions. I am yet to implement the solution and will keep you posted/accept the solution once I am done.
In the meanwhile, some light around the discussion you guys are having:
1. Aging Buckets or Bucket categories are fairly static for me. i.e., the buckets are pre-defined and we will not change them so often (other than updating the rates for the buckets as input, constantly) unless the nature of the business changes that we need to create new segmented rates/buckets to capture reality. This whole bucketed logic is used only for "extrapolation" of a curve purposes.What this means is that the bucket's min and max are justified to be hard-coded and it won't change over time.
2. The other fake time dimension "Aging Days" is a list instead of the time dimension for multiple reasons. The core reason is that it is driven by what I am trying to achieve.
I am trying to do "cohort modeling" wherein, for each day/month it "ages" in its own profile. Eg. Seasonal months have higher rates vs non-seasonal months and each month/day ages differently.
To understand more on the motivation or the larger problem I am working on,I would definitely request you to look at:
https://community.anaplan.com/t5/Anaplan-Discussions/Diagonal-sums-over-flat-list-dimension-on-Anaplan/m-p/40300#M4133
which would be interesting for you guys and gives an idea of why fake time dim is required.0 -
Perhaps what you say is better in terms of performance and prevents unwanted LOOKUPs.
I do have a fake time dimension that is a general LIST (not numbered - not sure why I chose a general LIST) that ranges from 0-1000 actually. And this becomes the "Aging Day" in my original post (where I mentioned it ranges from 0-365 for easier understanding).
This list is static and will not change. If so, should I create a property for each day and assign a bucket to this fake time dimension list and avoid creating another Buckets list, lookups etc? How would this work? I would like to be as efficient as possible (and I am only a beginner) as my model is currently HUGE due to cohort modeling.0 -
Thank you @LipChean_Soh
The solution you posted works! Nice thing there to create a Parent item for the list and leave it as blank. I was missing that and running into formula errors.
Anyhow, is there a better way to do this? As I mentioned further down in this post, my model is huge and I wish to be efficient.
My aging days is from 0-1000 and is a static list. Is it better to assign a "Bucket" property to this list and directly use it?
0 -
Hi visivasa,
Regarding your statement "Anyhow, is there a better way to do this? As I mentioned further down in this post, my model is huge and I wish to be efficient", which module or line item in the mock up i created might potentially result in the massive increase in cell counts?
I took a look at your other post, and i have a rough understanding of the cohort diagonal summation.
Perhaps you can try to further explain how the cohort diagonal summation problem interacts with the aging bucket problem?
Thanks,
LipChean
0 -
All
have a look at this post
It deals with a very similar problem of "tiering"
https://community.anaplan.com/t5/Anaplan-Discussions/Another-work-around-method/m-p/42250#M4513
David
1 -
Thanks @DavidSmith! I think the original solution proposed here is similar, wherein we use a list with the summary set to "Last Non Blank" that does the trick.0
-
What's the complete formula that ends with ITEM(Average.... in Curve module? I'm getting error in formula.
0
