topic Re: Expression Help in QlikView

Expression Help

Anonymous — Tue, 01 Mar 2016 19:05:52 GMT

Hi All,

I’m working on a XIRR calculation where I come across with some issues that I need some help. I want to add to below “ Trans Type]-=” to exclude where If [Trans Type]=’EMV’ AND [Date]=’11/30/2015’ Also, to include [Trans Type]=’Memo:’ AND [Date]=’12/31/2015’

In words, I want to exclude Trans Type-=’EMV’ with 11/30/2015 date; Also, only take Trans Type=’Memo:’ with 12/31/2015 date.

Below is my current expression.

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

Any input will be greatly appreciated!.

Thanks,

Frankie

Re: Expression Help

tresB — Wed, 02 Mar 2016 04:31:15 GMT

Try like:

=XIRR({<Company-={'ABC'},[Trans Type]={'Memo:'},[Date]={'12/31/2015'}>

<Company-={'ABC'},[Trans Type]={'EMV'},[Date]={'11/30/2015'}>}

[Amount],[Date])

Re: Expression Help

jonathandienst — Wed, 02 Mar 2016 04:59:03 GMT

I understood the OP a little differently:

=XIRR(

{

<Company-={'ABC'}, [Trans Type]={'Memo:'}, [Date]={'12/31/2015'}> +

<Company-={'ABC'}, [Trans Type]-={'Memo:'}> -

<Company-={'ABC'}, [Trans Type]={'EMV'}, [Date]={'11/30/2015'}>

}

[Amount],[Date])

(include trans type = memo only for the defined date,

include other trans types for all dates,

but exclude trans type EMV for the second defined date)

Re: Expression Help

Anonymous — Wed, 02 Mar 2016 16:12:10 GMT

Tresesco and Jon thanks for the reply. Will check it out shortly to see how it goes.

Many Thanks!

Frank

Re: Expression Help

Anonymous — Fri, 04 Mar 2016 15:50:03 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])

Re: Expression Help

settu_periasamy — Fri, 04 Mar 2016 16:12:37 GMT

Hi,

Try like this..

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