topic Re: Exclude Expression Help in QlikView

Exclude Expression Help

Anonymous — Fri, 04 Mar 2016 16:04:26 GMT

All,

Do you know how I can make it dynamic for the Company? I have multiple companies, but I just want to exclude certain companies that start/have the word "GP". I have this added to the expression, it didn't work. {"$(-={'GP,*'})"} .

=XIRR({<Company-={"$(={'GP,*'})"} ,Trans Type]-={'Income:' ,'Expense:', 'Fees'}>}[Amount],[Date])

Many Thanks,

Frank

Re: Exclude Expression Help

satishkurra — Fri, 04 Mar 2016 16:06:47 GMT

COuld you please share some sample data by that we can see to make the expression work?

Re: Exclude Expression Help

settu_periasamy — Fri, 04 Mar 2016 16:19:04 GMT

Try Like

=XIRR({<Company-={'GP*'},[Trans Type]-={'Income:' ,'Expense:', 'Fees'}>}[Amount],[Date])

Re: Exclude Expression Help

Anonymous — Fri, 04 Mar 2016 16:36:49 GMT

I tried, it didn't works.

For example:

I have the following companies:

ABC GP,LP

EFG INC,LP

XYZ GP,LP

I want to exclude these 2 companies where they have "GP"

ABC GP,LP

XYZ GP,LP

Thanks,

Frank

Re: Exclude Expression Help

settu_periasamy — Fri, 04 Mar 2016 16:43:49 GMT

You mentioned the word is in start.

try like this..

=XIRR({<Company-={'*GP*'},[Trans Type]-={'Income:' ,'Expense:', 'Fees'}>}[Amount],[Date])

It will show the red color line. you can ignore it. It will work

Re: Exclude Expression Help

Anonymous — Fri, 04 Mar 2016 16:59:40 GMT

Thanks.. it works.... I just I'm just missing * in the front.

Re: Exclude Expression Help

Saravanan_Desingh — Fri, 04 Mar 2016 17:12:33 GMT

Even you can use ~

<~Company={'*GP*'}..

http://help.qlik.com/en-US/qlikview/12.0/Subsystems/Client/Content/ChartFunctions/SetAnalysis/set-modifiers.htm

Forced exclusion

Finally, for fields in and-mode, there is also the possibility of forced exclusion. If you want to force exclusion of specific field values, you will need to use “~” in front of the field name.

sum( {$<~Ingredient = {“*garlic*”}>} Sales )

The field Ingredient is in AND mode.

Returns the sales for current selection, but with a forced exclusion of all ingredients containing the string 'garlic'.