FINDITEM formula gives blanks whereas I expect values
Hi Anaplan Community,
I have a strange behaviour in Anaplan.
I use finditem in 4 different line items (Site 1, Site 2, Site 3 and Site 4) . The finditem formula is similar for each line item, it just refers to another text-formatted line item to perform the finditem.
The result of the Site 1 formula looks good. However, Site 2, Site 3 and Site 4 return blanks and I don't understand why. Site 1 is working, so why not the other line items??
Can anyone explain what I am doing wrong?
Attached some print screens for the details on formulas and results.
Next to that some info on what I try to accomplish with this module:
I want to link SKUs to sites manufacturing the SKUs.
I have a source module with the SKU-list and the Site-list as dimensions in which volumes are entered by site leads on SKUs. So wherever there is a volume on a SKU/site combination, I know the site produces that SKU.
I want to summarize that information on SKU-level, so for each SKU I want to mention which sites produce it. That is why I use the TEXTLIST formula to list the sites as text and then I break them back to individual sites.
If anyone has a better idea to accomplish this, please let me know. I am not sure if this is the right approach.
Thank you!
Kind regards, Louise
Best Answers
-
Love the formulas here. My best guess is that you're picking up a space in the parsing. You can check that by looking at the length of the Site 2 text for instance. If you're all good on the parsing then make sure the list item is in the list and spelled correctly without any special characters.
Just some ideas.
0 -
In that post I sent you, did you read down to where I explained why not to use TextList() and how to get around it? It starts with...
Rob
0
Answers
-
@JaredDolich , thank you for your quick response.
You were right, looking at the length of the string revealed that it was taking into account spaces.
Change the site x (text) formulas to starting position "+2" instead of "+1" and the length from "-1" to "-2" did the trick!
Thank you for your help!
1 -
"I want to summarize that information on SKU-level, so for each SKU I want to mention which sites produce it."
A better way to do this would be to have a boolean where volume > 0 in your source module. It would show all the sites for each SKU rather than limiting it to 4. Pivot the view so you have SKU's on rows and Sites as columns.
This use of textlist and subsequent text manipulation will have a large performance cost.0 -
I can emphasize enough of what @MarkWarren stated about the use of TextList(). TextList() is evil on performance and there is a reason why we have a rule dedicated to it (2.02-11).
Also, you might want to check out this thread on how to get around it....
https://community.anaplan.com/t5/Anaplan-Platform/Lookup-Sum-and-Textlist-Cheat-Sheet/td-p/53085
Rob
1 -
Hi @MarkWarren ,
Thank you for the suggestion.
I know which SKU is produced by which sites, I have this information in a module with SKUs list as a dimension and Sites-list as a dimension.What I want to do is to move that information into a module which only has SKUs as a dimension. So I want to bring the site into a line item. Usually a SKU is only produced in one site. There will be few exceptions where it is produced in multiple sites, then I would just need to have a boolean line item flagging that it is a multi-site sourced SKU.
I don't see how I could use the SUM and boolean trick to accomplish this...
0 -
0
-
hi @rob_marshall ,
I took a look at that cheat sheet post, but I don't think I understand how I get around the use of textlist.
I use the textlist as is explained in the post, this gives me all sites where the SKU is produced in a text-formatted line item, for each SKU in my target module. This is what I need. I then use finditem to split the text value into multiple text values if the SKU would be produced in more than one site.
I don't know how I could use SUM or LOOKUP to do the job.
Would appreciate any help!
0 -
Hi Rob,
Oh, now I see, I did not read through to the bottom initially.
It took me some time to "see the light", but now I figured out! Thank you so much!
I am now using the firstnonblank to show the first site that makes the SKU. Next to that I have added a number line item in my source module and I use a SUM in my target to check how many sites produce the SKU. If that number is higher than 1, I flag that SKU as a multi-sourced SKU. Attached you can see the comparison finditem vs textlist. I am now only showing the first site and indicating if there are multiple. which is sufficient.
I will now remove the textlist and the finditems to save space 🙂
Thank you for your help!
0 -
0
