4 Replies Latest reply: Aug 3, 2010 8:24 AM by Goran Korsgren

# Set analysis help needed - Word that starts with or include

Hi guys

I have an item master. Each item has an ITEMCODE. I want to do an EXPRESSION in a STRAIGHT TABLE as the following:

=sum(COST) to get a VALUATION of my stock. The problem is that I only want the valuation of items that has a certain ITEMCODE.

I only want the value of items that starts with a "F" or "FZA". I only know how to do a set analysis on a whole ITEMCODE (see below).

Obviously would this not help as I will have to list every ITEMCODE I want. Is there a way that I can do a set analysis on an ITEMCODE that starts with "F" or "FZA"?

sum({<ItemCode={F10024000'}>} Cost)

• ###### Set analysis help needed - Word that starts with or include

I have only used set analyis when looking for an exact value e.g. Sum({<dsc={'FSA'}>} VALUATION)

In this instance can you try using an if statement

sum(if(left(ITEMCODE,3)='FZA' or left(ITEMCODE,1)='F',VALUATION))

• ###### Set analysis help needed - Word that starts with or include

Thank you

This worked perfect

• ###### SV:Re: Set analysis help needed - Word that starts with or include

If you really wants COST for items like you specified: ITEMCODE that starts with "F" or "FZA"

you only need ITEMCODE that starts with "F" (everyone agree that "FZA" starts with "F" OK?)

so

sum(if(left(ITEMCODE,1)='F',COST))

would do, it will give the same result as

sum(if(left(ITEMCODE,3)='FZA' or left(ITEMCODE,1)='F',COST))

• ###### Set analysis help needed - Word that starts with or include

Hello Christo,

For what it's worth, set analysis would work like

sum({<ItemCode={"F*","FZA*"}>} Cost)

Take into account that "F*" will return FZA* results as well.

Regards.