ISFIRSTOCCURRENCE not providing the correct result

jimfeng5 · 2024-07-25T21:10:54+00:00

There was an error rendering this rich post.

Hi Anaplanners,

I was trying to setup a simple isfirstoccurrence formula and I couldn't get to the correct result:
In screenshot below, I have the module dimensioned by #P3b_Child Territory, which has a parent. I want to use the isfirstoccurrence formula to only show true for the first appearance of "Americas" in the LI "Region (Source Territory) for the parent, but it's not doing that. Does anyone spot anything wrong with my setup?

Accepted answers

JMVasicek

So the goal is to flag the first occurrence of a 'Region (Source Territory)' value within each parent? So 'Americas' could be present in two children of Parent A and two more in Parent B, and you'd want just ParentA|Child1 and ParentB|Child1 to show as IFO?

If that's the case, you'd need to concatenate the code of P3 Territory (ideally in a P3 Territory System module, with a delimiter already appended to the end of it) and the 'Region (Source Territory)' line item to a new line item (P3 Territory|Region) and run ISFIRSTOCCURRENCE on the concatenated line item.

Apologies, I should have started by asking - what are you accomplishing with this? Is it to create new list members? (If so, then the option above works especially well because you'd need the concatenated code anyway)

JMVasicek

Ah, thank you for the background @jimfeng5.

Would it be possible for the children of a parent to cover multiple regions? (Unlikely, but sometimes the case.)

If not, you might be better served using a FIRSTNONBLANK aggregation formula at the parent level into a line item there. (Something like Region[FIRSTNONBLANK: P3 Territory]). If the region attribute exists as a list member, that would be even better.

All comments

JMVasicek

Hi @jimfeng5 I have two quick questions:

Is 'Americas' the region for only those two child members, or are there other children that have a Region of 'Americas'? Despite the display order, whichever list member comes 'first' on the back end of the list (even if it's in a different parent) will show as IFO.
Do 'Region (Source Territory)', 'IFO: Region (Source Territory)', and the list referenced in the IFO formula all the same list or subset?

jimfeng5

@JMVasicek ,

Is 'Americas' the region for only those two child members, or are there other children that have a Region of 'Americas'? Despite the display order, whichever list member comes 'first' on the back end of the list (even if it's in a different parent) will show as IFO.

Yes you are right, I do see another child members (though not in order) flagged as true.

Do 'Region (Source Territory)', 'IFO: Region (Source Territory)', and the list referenced in the IFO formula all the same list or subset?

yes, both 'Region (Source Territory)' and 'IFO: Region (Source Territory)' are LIs with only the dimension of #P3b_Child Territory (Overlay)

How do I revise this formula so that it'll flag TRUE for the first occurrence within a Parent?

JMVasicek

If you always want to flag the first child within every parent, you can modify the first argument of your IFO formula to reference the parent of #P3b Child Territory (Overlay). In this case, it appears that would be the line item 'P3 Territory'.

If there are more conditions than that (e.g., the first Child Territory with >10k in sales) you would need a staging line item first.

jimfeng5

@JMVasicek , apologies, let me rephrase, I want to flag the first occurrence of a new value within a parent. In the example shown above, I would only flag the first child as TRUE since both first child and second child have "Americas" as Region.

JMVasicek

jimfeng5

Hey @JMVasicek , thanks for getting back to me on this.

The goal of this is to bring the unique Region attribute from child to the parent. and to display as an LI on the dashboard to show end users which region(s) this parent covers. In this case, when a parent has multiple childs all cover the same region, then we only want to display that region once at the parent level.

JMVasicek

Ah, thank you for the background @jimfeng5.

Would it be possible for the children of a parent to cover multiple regions? (Unlikely, but sometimes the case.)

jimfeng5

@JMVasicek , yes it is possible for a parent to have multiple childs with different regions. This is due to the coverage of the parent being global rather than local.

Quick Links

Can you help?

Unanswered questions

Hide the cards under "Models" section once connected
Hi Is there a way to hide the cards under the "Models" section once connected, as I would like the users to see only the links to the apps? Thank you
OData Rest API for
Hi, Does anyone know how to get an outbound OData Rest API, (OAuth2) to establish a connection between SAP and Anaplan. Thanks Khaled
Looking for Anaplan End-Users to take a short survey!
We are looking for Anaplan end-users to provide feedback on their experiences with the Excel add-in. Interested individuals will respond to this 5-minute survey to help us understand personal needs and behavior when using the add-in. The feedback provided by survey takers is essential to the roadmap of Anaplan's products.…