FIRSTNONBLANK for Numbers

ArunManickam
Master Anaplanner/Community Boss

FIRSTNONBLANK for Numbers

Can First Non Blank formula be used for numbers as mentioned. ArunManickam_0-1604284502692.png

 

When i try to, i get an error as follows,

ArunManickam_2-1604284611631.png

Let me know if you are able to successfuly use this formula for numbers.

 

Thanks

Arun

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Misbah
Moderator

Re: FIRSTNONBLANK for Numbers

@ArunManickam 

 

No way, as far as I know we can not use FNB on number formatted line items. It has to be removed from Anapedia as It will create confusion to many. Thanks for pointing this out.

 

Misbah

View solution in original post

9 REPLIES 9
Misbah
Moderator

Re: FIRSTNONBLANK for Numbers

@ArunManickam 

 

No way, as far as I know we can not use FNB on number formatted line items. It has to be removed from Anapedia as It will create confusion to many. Thanks for pointing this out.

 

Misbah

View solution in original post

rob_marshall
Moderator

Re: FIRSTNONBLANK for Numbers

@ArunManickam @Misbah 

 

Just a thought, did you try to change the summary status to None?  

Misbah
Moderator

Re: FIRSTNONBLANK for Numbers

@rob_marshall 

 

Yes! and as expected It doesn't work.

rob_marshall
Moderator

Re: FIRSTNONBLANK for Numbers

@Misbah 

 

Yep, just tested it out and you are correct, it does not work

ArunManickam
Master Anaplanner/Community Boss

Re: FIRSTNONBLANK for Numbers

@rob_marshall 

 

I tried with all summary options, it dint work.

 

Thanks

Arun

rob_marshall
Moderator

Re: FIRSTNONBLANK for Numbers

@ArunManickam 

 

Correct

thanasis
Occasional Contributor

Re: FIRSTNONBLANK for Numbers

Hi,

Can you use the TEXT() within FNB?

Thanos
rob_marshall
Moderator

Re: FIRSTNONBLANK for Numbers

@thanasis 

 

Yes, you can.

DaanishSoomar
Certified Master Anaplanner

Re: FIRSTNONBLANK for Numbers

Would a workaround to this issue be converting the number to text? Then using FIRSTNONBLANK against the text and then converting it back to number?

 

I have a use case where I have 3 modules.

 

I have 1 module that is: 

 

2 flat lists x line items (Amount- number). (Acquirer and Product flat are the lists). 

I have another module that is:

 

L4 Acquirer (which rolls up to L1 Product) x line items (Amount- number) 

 

Lastly I have a System module that is:

 

L4 x line items (Acquirer & Product flat properties)

 

If I want to allocate the amount found at intersections in the module with 2 flat lists and apply it to only 1 row of the hierarchy, would firstnonblank against the properties get me there?

 

Right now the formula is Amount module.amount[LOOKUP: SYSModule.Product, SYSModule.Acquirer].

Problem is the amount is being applied to all members that return the same Product and Acquirer, I only want it to apply to 1.

 

I.e. if I want to apply $1M to Product A, Acquirer A, instead of showing as $1M in the L4 module, I am seeing $12M because there are 12 members in the hierarchy with Product A and Acquirer A.

 

Look forward to hearing suggestions. Also ideally this solution would be quick (even if not truly best practice) would prefer a hot fix rather than a full architectural redesign as I have explored that route but we don’t have much time with the client as I am a bit tight on project deadline.