Pick list item based on property and use it as a dimension/LOOKUP

visivasa

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?

Accepted answers

LipChean_Soh

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

Bucket List.png Curve Blueprint.png Curve Data.png

All comments

LipChean_Soh

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

Bucket List.png Curve Blueprint.png Curve Data.png

Jaakko

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

LipChean_Soh

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

Jaakko

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.

visivasa

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.

visivasa

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.

visivasa

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?

LipChean_Soh

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

DavidSmith

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

visivasa

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.

Ms_Peach

What's the complete formula that ends with ITEM(Average.... in Curve module? I'm getting error in formula.

visivasa

@Ms_Peach

Its just

" .... ITEM(Aging Bucket) ELSE BLANK "

The blank is necessary as the summary method for this line item is set as "First non-blank" which is a key part of the solution provided by LipChean_Soh

Quick Links

Can you help?

Unanswered questions

Batch Script
Hello team, I am logging into Anaplan through SSO. I would like to create a batch script to import a file into Anaplan. For the Spoke model, I would like to run an process via a batch script to import data from the Data Hub. Can anyone provide me script for both the condition.
import monthly data with months in columns
is there a way to import data with months in columns (usual excel table view)? I have to transform the data so there are rows to cover each period in the upload data form, but I am wondering if there is a better way to just import the data as is with the months in the columns. please let me know what you think. thank you,
Cloudworks Big Query Integration Fail
Hello, I am receiving an 'Anaplan Upload Failed' Status Description when testing my integration with a Big Query dataset. The integration imports data from BQ to our Anaplan model. No other details given in the error log. I suspect that Cloudworks is not even picking up the file but am not sure what we did wrong on the set…