Need Help for XIRR in qliksense

[Shamshad]

Hi All,

I have the below data and want to calculate XIRR. (note I have attached QVF which gives all this data)

My rule for calculating XIRR is  as below

take amount from begining to start date -i.e opening balance
+
take amount of all transaction between start date to end date-Transactions
+
take amount from beginning to end date - closing balance

I am explaining in more details-----------
Lets say user selected start date as 4/21/2021 & End date as 7/27/2021

I want to calculate xirr by adding Opening balance, transactions(sum(Amount)) & Closing balance on date selected by user,just like below

=XIRR(Aggr(
$(vOpening) +
Sum(Amount) +
$(vClosing),ID,Date),
Date)

vOpening expression

Sum({<Account={13405},Date={">=$(=Min({1} [Date]))<$(=Min([Date]))"}>}(Amount ))

vClosing expression

Sum({<Account={13405},Date={">=$(=Min({1} Date))<=$(=Max(Date))"}>}(Amount * -1))

So i have created 2 variables 1 for opening & other for closing .
If i add opening,transactions & closing in kpi its showing correct amounts separately but when i am adding in xirr formulae its not giving me results.

No result for XIRR...😥

I realize this may be a little confusing to follow so I have attached a QVF which shows what i achieved i.e opening,Transactions & closing and what i want i.e XIRR.

I have also attaching excel file where i calculated the XIRR.

Any help is much appreciated!!! 

Thanks

[Shamshad]

Hi,

Could anyone please help me  to resolve above issue,i tried everything but not getting the result.

Thanks