topic Re: Expression Help -2 in QlikView

Expression Help -2

Anonymous — Wed, 09 Mar 2016 19:12:03 GMT

Hi All,

I'm working on an XIRR expression that I need some help. I created a variable for "Amount" where user can increase or decrease the value for particular date. I want to aggregate this variable amount to the XIRR calculation. Please see attached Excel spreadsheet.

=XIRR(Amount,IRR_Date)

Many Thanks,

Frank

Re: Expression Help -2

jagan — Thu, 10 Mar 2016 02:47:58 GMT

Hi,

Try like this

=XIRR(If(IRR_Date = Makedate(2015, 12, 31), vIRR, Amount),IRR_Date)

Regards,

Jagan.

Re: Expression Help -2

Anonymous — Thu, 10 Mar 2016 02:58:35 GMT

Thanks ! Jagan,

Not close to the expected IRR % if 12/31/2015 decreased by 30,000,000 to 270,000,000. Did you check out my Excel spreadsheet manual calculation? For IRR calculation, I use this function XIRR(Amount, IRR_Date) both of the Amount and IRR_Date are in series of value and series of dates.

Re: Expression Help -2

jagan — Thu, 10 Mar 2016 03:14:55 GMT

Hi,

Check this

=Num(XIRR(If(IRR_Date = Makedate(2015, 12, 31), Amount - Fabs(vIRR), Amount),IRR_Date), '#,##0.00%')

Regards,

Jagan.

Re: Expression Help -2

Anonymous — Thu, 10 Mar 2016 03:49:14 GMT

Thank You Jagan!!! It works!!

How can I make it more dynamic if I have 2 different dates and 2 different variables?

Re: Expression Help -2

jagan — Thu, 10 Mar 2016 03:52:54 GMT

Hi,

Get dates from Input box and replace this in the expression like below

=Num(XIRR(If(IRR_Date = vDateVariable1, Amount - Fabs(vIRR1),

(If(IRR_Date = vDateVariable2, Amount - Fabs(vIRR2),

Amount)),IRR_Date), '#,##0.00%')

Hope this helps you.

Regadrs,

Jagan.

Re: Expression Help -2

Anonymous — Thu, 10 Mar 2016 04:00:11 GMT

Thanks again. Will check it out.

Re: Expression Help -2

Anonymous — Thu, 10 Mar 2016 15:21:18 GMT

Jagan,

Let me know if you can help.

I have excluded fields in my xirr calc. I want to incorporate your expression with below. Many Thanks!

XIRR({<Company-={'*XYZ.*'},[Type]-={'Income:,'Expense:'}>}[Amount],[IRR_Date])

=Num(XIRR(If(IRR_Date = Makedate(2015, 12, 31), Amount - Fabs(vIRR), Amount),IRR_Date), '#,##0.00%')

Re: Expression Help -2

jagan — Fri, 11 Mar 2016 00:59:21 GMT

Hi,

Try like this

=Num(XIRR(If(IRR_Date = Makedate(2015, 12, 31) AND Company NOT LIKE '*XYZ*' AND [Type] <> 'Income:' AND Type <> 'Expense:', Amount - Fabs(vIRR), Amount),IRR_Date), '#,##0.00%')

Hope this helps you.

Regards,

Jagan.

Re: Expression Help -2

Anonymous — Fri, 11 Mar 2016 18:32:48 GMT

Thanks Again!