topic Re: Calculate interest on interest in script in QlikView

Calculate interest on interest in script

Tue, 12 May 2015 12:45:37 GMT

Hi,

Is there an easy way to calculate interest on interest in the script, per date and aggregated over a set time period.

The interest or return will differ for each date and is shown as a per cent where below 1 is negative interest and above 1 is positive interest.

Example

0,990,99

Date	Interest
2015-01-01
2015-01-02	0,95
2015-01-03	1,1
2015-01-04	1,04
2015-01-05	1,01
2015-01-06	1
2015-01-07	0,98
2015-01-08	0,96
2015-01-09

For each row the accumulated interest on interest should then be calculated.

In a chart I am using I use

(exp(sum(log(Performance ))))

Now, I want to precalculate this so every date get its accumulated return on return (interest on interest) for a set time period. In reality my time period will be one year, but in this example we can use today -2 days.

So in the above example 2015-01-03 should get 1,03455 ( 0,99 * 0,95 * 1,1)

while 2015-01-04 should get 1,0868 (0,95 * 1,1 * 1,04).

Any ideas?

Re: Calculate interest on interest in script

petter — Tue, 12 May 2015 15:46:36 GMT

Sure - you will have to create a calculated field in your LOAD statement. This should use the Peek()-function to refer to the last loaded value. You add that to the current interest for the date and row being read. You will have to make sure you make the LOAD in sorted order by ascending date...

Re: Calculate interest on interest in script

petter — Tue, 12 May 2015 16:18:09 GMT

like this:

LOAD

InterestDate,

Interest,

If( RecNo() = 1 , 1 , Peek('AccInt') ) * Interest AS AccInt

RESIDENT

DateAndInterestTable

ORDER BY

Date;

Re: Calculate interest on interest in script

MarcoWedel — Tue, 12 May 2015 23:23:26 GMT

Hi,

one solution could be:

tabTestData:
LOAD Date(Today()-1000+IterNo()) as Date,
    Num(Round((Rand()*0.2)+0.9,0.01),'0.00') as Interest
AutoGenerate 1
While IterNo()<=1000;

tabData:
LOAD *,
    IoI1/IoI2 as InterestOnInterest;
LOAD Date,
    Interest,
    Interest*Alt(Peek(IoI1),1) as IoI1, 
    Alt(Peek(Interest,-365),1)*Alt(Peek(IoI2),1) as IoI2
Resident tabTestData;
   
DROP Table tabTestData;

(just drop the auxiliary fields IoI1 and IoI2 if not needed)

hope this helps

regards

Marco

Re: Calculate interest on interest in script

Wed, 13 May 2015 08:33:34 GMT

Thanks Marco. That seems to work fine. In my solution I have a great number of stocks to calculate this and different scenarios, but I guess that can be handled by grouping and sorting.

I also have problems with the -365 as I done have interests on all dates, any hints there or do I have to fill out the empty dates with interest of 1?

As of Petters solution, I think that will work to calculate interest over time, but it does not take the 365 day cap into account.

Thanks for the suggestion though.

Kind regards

Peter