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.