Usage of First Occurrence formula with conditionals

PauloM
PauloM
in Modeling

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:

image.png

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!

Tagged:

Best Answers

  • pyrypeura
    pyrypeura
    Answer ✓

    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
    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.

Answers

Categories