Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
fanko1225
Contributor

Need 2 Date Ranges for XIRR calc - Help

 

Hi All,

 

 

I’ve been struggle to calculate XIRR with couple date range selection. In current process, I calculate inception to date base on the transaction date and cash flow and it works fine. I wanted to tweet it a bit to add 2 selection dates where user can define. To calculate XIRR, the first cost/payment needs to be a negative.

What I want to accomplish: – 2 Date selection objects

  • Need to take Date selection # 1 prior date “CashFlow” value and turn into negative for XIRR calculation.
  • This will serve as my staring point first cost/payment

For Example: If I want to check the XIRR % from 1/1/2015 – 9/30/2015 (base on “Reporting_Date”)

#1 –select Date Range 1/1/2015 base on “Reporting_Date”

 

#1_a – as I said above regards to negative. I need 12/31/2014 where Flow Type = EMV as my starting point and this value needs to be negative (-1,100,000)

#2 –select Date Range 9/30/2015 base on “Reporting_Date”

#3- XIRR Caculation - xirr(CashFlow, Transaction_Date)

 

 

Any help will greatly appreciated!

Thank You,

Frank

 

2 Replies

Re: Need 2 Date Ranges for XIRR calc - Help

Hi,

I think you need to look on the expression with aggregation. Not sure, But you can try something like this..

=XIRR(

Aggr(If(RowNo(TOTAL)=1,

          Sum({<FlowType={'EMV'}>}CashFlow)*-1,Sum(CashFlow)),

          Transaction_Date,Company),Transaction_Date)

You just play with the expression, based on your need..

fanko1225
Contributor

Re: Need 2 Date Ranges for XIRR calc - Help

Thanks for the sample expression. I will play around with the expression.