I am a beginner in Anaplan usage and trying to setup a simple module to track the age buckets of our assets. I defined a general list which contains the asset id, age in years of the asset and location of the asset. I am trying to populate the age range of the asset in the age bucket column. If the asset age is less than 1 year,then the age bucket will be >1. If the age is between 1 and 2 years, then the range will be 1>2 and so on.
I have setup the age bucket column as text format.My question is how to populate the age range?
Can you elaborate a bit more? For example how many age buckets do you have and what are the ranges?
The most simple way to form a string is to have a formula like this:
IF Age < 1 THEN "<1" ELSE IF Age > 1 AND Age < 2 THEN "1>2" ELSE "" etc
This works if you have only a few age buckets which can be put into the conditional statement of the formula. If you have lots of age buckets, you can also try to build more dynamic formula if possible. For example:
IF Age < 1 THEN "<1" ELSE TEXT(ROUND(Age, 0, DOWN)) & "<" & TEXT(ROUND(Age, 0, UP))
This would always result in two subsequent integers (1.5 would lead to "1<2" and 3.7 would result "3<4").
If you plan to make formulas or logic around age buckets, consider making the age bucket property list formatted and create a list named "age bucket" with members "<1", "1<2" etc. Then your formula would look something like this:
IF Age < 1 THEN Age Bucket.'<1' ELSE IF Age > 1 AND Age < 2 THEN Age Bucket.'1>2' ELSE BLANK
With finditem function you could make this option more dynamic.
We have 6 types of age buckets. I followed your suggestion to have the age bucket property list formatted and defined a list for Age Bucket and used below formula to populate the age bucket in Asset table.
IF Age in Years < 1 THEN Age Bucket.'<1' ELSE
IF Age in Years > 1 AND Age in Years < 2 THEN Age Bucket.’1>2’ ELSE
IF Age in Years > 2 AND Age in Years < 3 THEN Age Bucket.’2>3’ ELSE
IF Age in Years > 3 AND Age in Years < 4 THEN Age Bucket.’3>4’ ELSE
IF Age in Years > 4 AND Age in Years < 5 THEN Age Bucket.’4>5’ ELSE
IF Age in Years > 5THEN Age Bucket.’>5 ’ ELSEBLANK
But I am getting invalid formula error. I have attached the screen shot of the setup for Age bucket list.
Thanks for your reply. Yes, it worked after correcting the formula.
Now, I want to get the sum of the assets by their age bucket. i.e. How many assets are less than 1 years old and so on so forth. I added a column ' Counter' in the asset details list and put the value as 1 for all tye records.
I have another list named Asset Summary where I will have the age buckets as individual columns. My idea is,for a given age bucket use the SUM formula to get the sum of counter column in the asset details list and show that in the Asset Summary list. Would appreciate any help on this
I am able to get the sum of the assets by each age bucket in the module 'Total Asset onhand'. please see the screen shot attached.
Now I am creating one more module 'Planning' where I want to pull up the sum of the assets which are more than 4 years old. For this, should I create the age bucket (>4) as a line item in the module 'Total Asset onhand'. and use that line item in my module 'Planning'? If yes, what should be the formula for that new line item (>4) in the module 'Total Asset onhand' to get the sum of the assets which are more than 4 years old?
It's difficult to say without looking at the lists and blueprints, but one thing you could do is add a property to the asset listthat checks to see if the asset is greater than 4 years old. Then in the module that has the values you would like to sum, add a line item that says if the greater than 4 years old property is true, then take the value else 0. Then in the planning module, you could use a select function to grab the top level of the asset list from the >4 year old line item you created which would only contain the >4 year old values. The last formula would look like Module Name.'Greater than 4 Year Old Line Item Name'[select: asset list.Top Level Item]. Let me know if this helps at all.