topic Re: IRR fuction in QlikView

IRR fuction

Jamal — Wed, 25 Nov 2020 16:16:04 GMT

Hi Friends,

I need your help please, I struggle with the IRR function, here is my sql server table:

I want to calculate the the internal rate (IRR) for each Projet. I ve used =RangeIRR($(vinvet),$(repeat(vpayment &',',$(vNumber-of-payment)))) but is not working. I need to get those variable from SQLServer and calculate the IRR. thank you very much

projet	invest	payment	Number-of- payment
A	-5000	200	12
B	-3000	600	25
C	-7000	500	36

Re: IRR fuction

tincholiver — Sun, 03 Feb 2019 19:14:57 GMT

Hi Jamal, try with this scripting:

Result is:

Payments:
LOAD
'Pays' as Pays,
projet,
payment as CashFlow,
payment,
[Number-of- payment]
FROM [$(Data)Libro.xlsx](ooxml, embedded labels, table is Hoja4);

TempTable:
LOAD
Max([Number-of- payment]) as MaxPay,
Min([Number-of- payment]) as MinPay,
Pays
Resident Payments Group By Pays;

LET vMax= Peek('MaxPay',0,'TempTable');
LET vMinPay= Peek('MinPay',0,'TempTable');
SET vMin='1';

NoConcatenate
Final:
LOAD
'$(vMax)' as MaxPay,
'$(vMinPay)' as MinPay,
projet,
//invest,
CashFlow,
payment,
[Number-of- payment]
Resident Payments;

Left Join
LOAD
(IterNo()+$(vMin)-1) as Pagos
AutoGenerate 1 While IterNo()+$(vMin)-1<=$(vMax);

DROP Table TempTable;
DROP Table Payments;
STORE Final into $(Data)Payments.qvd(QVD);
DROP Table Final;

Payments:
LOAD
projet,
invest as CashFlow,
invest
FROM [$(Data)Libro.xlsx](ooxml, embedded labels, table is Hoja4);

Concatenate
LOAD
MaxPay,
MinPay,
projet,
CashFlow,
payment,
[Number-of- payment],
Pagos
FROM [$(Data)Payments.qvd] (qvd) Where Pagos<=[Number-of- payment] ;

let me know if the solution helped you

Re: IRR fuction

Jamal — Mon, 04 Feb 2019 13:35:48 GMT

Hi Tincholiver,
Yes the solution is working perfectly, Thank you very much for your help.
if I want to calculate this IRR for table with 126 000 client, I think it s gonna take a lot of memory and time.

Re: IRR fuction

tincholiver — Mon, 04 Feb 2019 14:12:21 GMT

Hi Jamal, it shoudn't because the scrip just load data, not make complex calculation.

Actually I try in my machine with 100.000 and 200.000 records and works good.

If it seemed a good solution, could you mark it as resolved so we can use it in furute cases?

Re: IRR fuction

Jamal — Mon, 04 Feb 2019 16:06:13 GMT

Hi Bro,

Ok, thank you very much. I appreciate your help.

Re: IRR fuction

tincholiver — Mon, 04 Feb 2019 16:55:12 GMT

your are welcome.
Regards.

Wicham..