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,


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









Best Answer

  • Misbah
    Answer ✓



    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.




  • @ArunManickam @Misbah 


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

  • @rob_marshall 


    Yes! and as expected It doesn't work.

  • @Misbah 


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

  • @rob_marshall 


    I tried with all summary options, it dint work.




  • Hi,

    Can you use the TEXT() within FNB?

  • @thanasis 


    Yes, you can.

  • 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.