Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join us at the Cloud Data and Analytics Tour! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
Jamal
Contributor
Contributor

IRR fuction

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

projetinvestpaymentNumber-of- payment
A-500020012
B-300060025
C-700050036
1 Solution

Accepted Solutions
tincholiver
Creator III
Creator III

Hi Jamal, try with this scripting:

Result is: 

Sin título.png

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

View solution in original post

5 Replies
tincholiver
Creator III
Creator III

Hi Jamal, try with this scripting:

Result is: 

Sin título.png

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

View solution in original post

Jamal
Contributor
Contributor
Author

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.

tincholiver
Creator III
Creator III

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?

IRR Solution 2.pngIRR Solution.pngIRR Solution 3.png

Jamal
Contributor
Contributor
Author

Hi Bro,

Ok, thank you very much. I appreciate your help.
tincholiver
Creator III
Creator III

your are welcome.
Regards.

Wicham..