How to get First Non blank value from a line item which has no summary settings
I have a module that is dimensioned by Product List. Two line items are added, one is Product List formatted, while the other is text formatted(this is not dimensioned by Product). Product line item will have only one product at a time. I want that non blank value in "First non blank" line item. How to achieve this?
Short answer is TEXTLIST() https://help.anaplan.com/c1e24970-400f-43af-95e7-b34a43253cdb-TEXTLIST
Or another version of it [TEXTLIST: ] I think the explanation of it much simplier here: https://help.anaplan.com/291bc437-9d30-48a4-a839-5246009fa0f2-x[TEXTLIST:y]
It works as a SUM, but for text. So you would need additional dummy list with a single element in it to use as your concatenation argument.
The following target module will take the value using [SELECT:Dummy list.Element]0
I dont think you can achieve that - the reason is simple
1. There are only 2 dimensions here - Product and time
2. You have only 12 cells left(no summary) when you remove product list - in text formatted line item
Alternate solution would be : -
to check on first occurrence and put a filter on "is not blank"
formula would be - "if isfirstoccurance(product, "your list in applies to") then name(product) else blank"
1.You should have product list as dimension in text formatted line item
2."product" is your line item here in formula
Please don't use TextList, there is a reason why there is a specific Planual rule for it (https://community.anaplan.com/t5/Chapter-2/2-02-11-Avoid-using-TEXTLIST/ta-p/80763)
Instead, please read through this thread as well at the one linked to it for ways to get around using TextList.
Thank you for the correction and addditional solutions. Good to know!1
Hi @Saisri8568 ,
I've put a method below which uses FIRSTNONBLANK instead of TEXTLIST.
1. First you want to create a dummy list with a single list item. In my example I use a dummy list called 'S' which contains the single list item 'Pull'.
2. Then you add a line item e.g. "Product Pull" in your module formatted by this dummy list with the formula "IF ISBLANK(Product) THEN BLANK ELSE S.Pull ".
3. Then create a new module (called 'New Module' in this example) with your dummy list as the only dimension. Create a line item e.g. 'Firstnonblank Product', formatted by the Products list, with the formula "Product Module.Product[FIRSTNONBLANK: Product Module.Product Pull] ". This will display the first non-blank item from the 'Product' line item in your original module.
4. You can then use the 'Firstnonblank' line item in your original module to reference this line item, using the formula "New Module.Firstnonblank Product[SELECT: S.Pull] ".
While you should be wary of directly referencing individual list items and using the SELECT function, in this case it shouldn't cause too many problems as you're referencing a dummy list which has only one item.
(@rob_marshall pull me up on this though if I've got it wrong!)
Hope this helps!
Why not go the simply route and put a top level on the Product list and change th summary of that line item to FirstNonBlank?
@JohnP Kudos and a gold star goes to you for thinking out of the box, well done.