Usage of First Occurrence formula with conditionals



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?


Best Answers

  • pyrypeura
    Answer ✓


    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
    Answer ✓

    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.