How to get First Non blank value from a line item which has no summary settings

Saisri8568
Saisri8568
in Modeling

Hi Everyone.,

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?

saisri_0-1644241433984.png

Tagged:

Answers

  • KirillKuznetsov
    KirillKuznetsov

    @Saisri8568 

    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]

  • ManjunathKN
    ManjunathKN

    Hi @Saisri8568 

    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"
    note -
    1.You should have product list as dimension in text formatted line item

    2."product" is your line item here in formula

    Thanks,
    Manjunath


  • rob_marshall
    rob_marshall

    @KirillKuznetsov @Saisri8568 

     

    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.

     

    https://community.anaplan.com/t5/Anaplan-Platform/Uses-for-TEXTLIST/m-p/120721#M30238

     

    Rob

     

  • KirillKuznetsov
    KirillKuznetsov

    @rob_marshall 

    Thank you for the correction and addditional solutions. Good to know!

  • JohnP
    JohnP

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

     

    JohnP_0-1644320202553.png

     

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

     

    JohnP_2-1644320351388.png

     

    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.

     

    JohnP_3-1644320809127.png

     

    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] ".

     

    JohnP_4-1644320932787.png

     

    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!

     

    Best wishes,

    John

  • rob_marshall
    rob_marshall

    @Saisri8568 

     

    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.

     

    Rob

Categories