Usage of First Occurrence formula with conditionals

PauloM

Hello,

I'm using the ISFIRSTOCCURRENCE formula to assist in creating hierarchies so the processes don't return a bunch of errors when generating the hierarchy, but now I'm facing a little issue, I want the first occurrence formula to return true only if other columns match some condition, but I can't find the correct formula for this.

Here's an example:

Product 3 don't need to be created on the hierarchy as it has no sales in Value 1, 2 or 3, but Product 1 does, unfortunately the first sale report for product 1 brings all zeroes, making the first occurrence always false.

Any ideas in how to trigger the FO formula only when the condition is met? Or maybe a better formula to do it?

Thanks!

pyrypeura

Hi,

To achieve you you will need to add additional line item called "Valid text" or something similar formatted as text. For that formula would be IF 'Value 1'<>0 OR 'Value 2' <>0 OR 'Value 3' <> 0 THEN TEXT ELSE BLANK and then do ISFIRSTOCCURRENCE formula to be based on that text line item.

jagadishdash

You have to do the two level of validation here. first validate that value is not equals to zero in all the column then do the ISFIRSTOCCURRENCE for the same.

PauloM

That solved the problem perfectly, thanks @pyrypeura and @jagadishdash.