ISFIRSTOCCURRENCE not providing the correct result
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?
Best Answers
-
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)
0 -
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.
0
Answers
-
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?
0 -
- 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?
0 -
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.
0 -
@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.
0 -
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.
0 -
@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.
0