Formula

UMAMAHESH

Hi Experts,

I am using two line items, Both line items are text format.

Line item 1: FCST_1

Line item 2: FCST_2

For suppose

FCST_1= 5.4591

Then

By using FCST_1 we need to calculate FCST_2= 5,46(Rounded value AND instaed of dot we need replace comma)

To do the requirement i write the below formula

FCST 2= IF FCST_1 = 0 THEN "0" ELSE LEFT(TEXT(FCST_1), FIND(".", TEXT(FCST_1)) - 1) & "," & MID(TEXT(FCST_1), FIND(".", TEXT(FCST_1)) + 1, 2)

By using above formula the result was FCST_2 = 5,45 (not a rounded figure) but we need to get a round off figure i.e 5,46

Can you please update the above formula for round the FCST_2 line item value.

Regards,

Uma

Accepted answers

All comments

alexpavel

Hi,

You could just put the formula in the TEXT formatted line item like this:

SUBSTITUTE(TEXT(ROUND(FCST_1, 2)), ".", ",")

LipChean_Soh

Hi,

Before we talk about the solution, i do want to talk about 'In Anaplan, just because you can, doesn't mean you should'.

All of us love Anaplan, because it's super flexible and we feel empowered. But we must realize that Text must ONLY be used where ABSOLUTELY NEEDED, and not as a means to capture everything from the source system (at least i think that's why you did what you did). Text takes up a lot of workspace, and it consumed a lot of server overhead in the background, dragging the overall performance of your model.

Please talk to whoever needed, be it the Database folks or the business users to make sure the source is number formatted before sending it over to Anaplan. I might be wrong in my assumption, and please correct me if i'm wrong by providing more context. Do explain the above effects to the client.

Having said that, please see if the following works. Do note that i'm using ROUND, VALUE keyword, and the 'Decimal Point' options in the number format page.

Screen Shot 2019-06-12 at 12.44.56 PM.png

Thanks,

LipChean

Quick Links

Can you help?

Unanswered questions

Rolling forecast
Hi , I have a requirement for my stakeholder to have a medium term forecast (by month for the next 6 months) and a more detailed rolling 15-week sales forecast by day which will then feed into the wider Supply Chain planning processes over time. This needs to be updated by the Sales representants for consolidation by the…
AnaplanXL Difficulties
Has anyone found success using Anaplan XL with Polaris models in a shared drive with or without concurrent users? My team has experienced a variety of errors, login failures, etc. sometimes it works, sometimes it doesn't. We've been recommended to save and edit files locally, which helps but certainly with its limitations…
Using Anaplan for Managing Custom Product Configurations (Example: Designer Dolls)
We’ve recently started using Anaplan to model and manage our supply chain and production planning for a niche ecommerce business that sells custom-designed dolls — each made-to-order with specific features, materials, and delivery lead times. A few interesting challenges we faced: SKU Explosion: Each doll can be customized…