Formula for Range of value

pnataraj

Hi 

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?

Is there any formula for that function?

Any pointer would be much appreciated.

 

 

Jaakko

Hi,

 

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.

pnataraj

Hello

Thanks a lot for your help here. 

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 > 5  THEN Age Bucket.’>5 ’ ELSE  BLANK

 

But I am getting invalid formula error. I have attached the screen shot of the setup for Age bucket list.

Can you please advise what I am missing here?

Thanks

Regards

Prabakaran

harish_bk

Hi Prabhakaran

 

Having code for each list item is not compulsory, further the result value should be list name and not list item code.

 

its should be >1<2 for the formula line in the second line than being the code value 1>2.

 

Further if the above solution does not solve the issue can you please share screen shot of your module

Kyle

Hi Prabakaran, 

 

The formula looks like it should work to me other than the names of the list items seem to be slightly different. You can go into the list and select them to ensure that the formatting is the same. Also make sure the line item is formatted as your Age Bucket list. If this still doesn't fix it, send us a screenshot of the blueprint and we should be able to figure it out. 

 

Hope this helps!

 

Kyle

pnataraj

Hi Harish

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

 

Thanks

Regards

Prabakaran

 

pnataraj

Hi Kyle

Thanks, After correcting the formula, the logic worked fine.

 

Regards

Prabakaran

Kyle

Hi Prabakaran, 

 

If you have a line item that has the Age Bucket list and the asset list as a dimension, then you can write a formula like Asset Details List.Counter[SUM: Asset Details List.Age Bucket]

This will then sum the counter up by the age bucket associated with the list item. 

Let me know if this works. 

 

Kyle

pnataraj

Thanks a lot Kyle, that worked..

pnataraj

Hi Kyle

 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?

 

Thanks a lot for your help 

 

Regards

Prabakaran

Kyle

Hi Prabakaran, 

 

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. 

 

Best, 

Kyle

pnataraj

Hi Kyle

Thanks for the response.

I was trying the below approach. I have the module Asset on Hand where I am having the age bucket as dimensional and have one line item  'Assets Onhand' which has the below formula 

Asset Tracking Details.Counter[SUM: Asset Tracking Details.Age Bucket].

Asset tracking Details is another module which has the  asset id, age in years, age bucket,etc  .

 

Now I need to show the Total of assets which are more than 1 year old in another module. I am using the below formual in this module.

Asset Summary Module.Summary[SELECT: Age Bucket.’<1’] . But I am getting invalid formula message.

Can you please throw some light in where exactly I am making mistake?

 

Thanks

Regards

Prabakaran