If-Then-Else statement within Set Anlaysis

[laurentubesing]

Hi All -

I was pretty proud that I got the following set analysis written successfully to pair down t he variability within my data set:

=left([Inv. Form Current Step], index([Inv. Form Current Step], '(')-1)

However, what I've found is that any field within "Inv. Form Current Step" that doesn't have a "(" is excluded.  So I was thinking I needed an If-Then-Else statement but don't know where to start? Can anyone help  me with that?

[JordyWegman]

Hi Lauren,

At first to make things clear, this is not set analysis. That would be something like:

Sum({$< Field = {'X'}>} Sales)

Where {$< Field = {'X'}>} is the set analysis.

What you can do is use an if statement, but that would be outside of your statement like this:

IF( Isnull(left([Inv. Form Current Step], index([Inv. Form Current Step], '(')-1)),
 PlaceHereYourNewCode,
left([Inv. Form Current Step], index([Inv. Form Current Step], '(')-1)
)

What this does is saying:

If the outcome of your first formula is null, then use your new formula not based on '(', but something else. If it is not null, then just use your normal formula.

Jordy

Climber

[laurentubesing]

Thanks for the help! I understand what you mean by this is not set analysis, thanks for clearing that up for me.

=IF( Isnull(left([Inv. Form Current Step], index([Inv. Form Current Step], '(')-1)),
[Inv. Form Current Step],)
left([Inv. Form Current Step], index([Inv. Form Current Step], '(')-1)

The above is giving me an error.  If the if statement is null, i would just want the value within [Inv. Form Current Step] to be displayed.  Would I need another function to get that action to occur?

Thank you!

Lauren 

[JordyWegman · Accepted solution]

No problem,

Then you need this:

=IF( 
Isnull(left([Inv. Form Current Step], index([Inv. Form Current Step], '(')-1)),
[Inv. Form Current Step],
left([Inv. Form Current Step], index([Inv. Form Current Step], '(')-1)
)

The [Inv. Form Current Step] should be outside the IsNull(). Abstract you have this:

=IF(
 IsNull(YourFormula),
 YourField,
 YourFormula
)

Jordy

Climber 

[laurentubesing]

That worked! Great thank you so so much!