Running Totals using Peek

[mjperreault]

Hi All,

I have the following Data 

Id	Date	Balance	Projected Revenue
1	1/1/2022	82,500,000.00	-
1	2/1/2022	72,187,500.00	-
1	3/1/2022	62,875,000.01	1,000,000.00
1	4/1/2022	51,562,500.01	-
1	5/1/2022	41,250,000.02	-
1	6/1/2022	30,937,500.02	-
1	7/1/2022	20,625,000.09	-
1	8/1/2022	10,312,501.37	-

And I want to create two new columns.

1. Projected Balance and 2. Factor 

1. Projected Balance will be Balance + Projected Revenue but should include any previous projected Revenue.  

ex. Projected Balance for all dates prior to 3/1/2022 will equal Balance while Projected Balance for all dates including and after 3/1/2022 will equal Balance + 1000000

2. Factor for all dates including and after 3/1/2022 will equal 1 plus Projected Revenue on 3/1/2022 divided by Balance on 3/1/2022

1 + (1000000/62,875,000.01)

My final table should look like this 

Id	Date	Balance	Projected Revenue	Projected Revenue	Factor
1	1/1/2022	82,500,000.00		82,500,000.00	1
1	2/1/2022	72,187,500.00		72,187,500.00	1
1	3/1/2022	62,875,000.01	1,000,000.00	63,875,000.01	0.015905
1	4/1/2022	51,562,500.01		52,562,500.01	0.015905
1	5/1/2022	41,250,000.02		42,250,000.02	0.015905
1	6/1/2022	30,937,500.02		31,937,500.02	0.015905
1	7/1/2022	20,625,000.09		21,625,000.09	0.015905
1	8/1/2022	10,312,501.37		11,312,501.37	0.015905

I've managed to create Projected Balance using the below but am struggling with the Factor calculation. Additionally was wondering if there is a way to do this in just one load without the preceding load to improve run time when moved to a larger data set.

Set NullInterpret = '';

Table:
Load * Inline [
Id,Date,Balance,Projected Revenue
1,1/1/2022,82500000.00,
1,2/1/2022,72187500.00,
1,3/1/2022,62875000.01,1000000.00
1,4/1/2022,51562500.01,
1,5/1/2022,41250000.02,
1,6/1/2022,30937500.02,
1,7/1/2022,20625000.09,
1,8/1/2022,10312501.37,
];

Table1:
Load *,
If([Id] = Previous([Id]),RangeSum(Balance,[Running Projected Revenue]),Balance) as [Projected Balance]
;
Load
Id,
Date,
Balance,
[Projected Revenue],
If(Id = Previous(Id),RangeSum([Projected Revenue],Peek([Running Projected Revenue])),RangeSum([Projected Revenue])) as [Running Projected Revenue]
Resident Table;
Drop Table Table;
Exit script

Thanks!

Mark

[mjperreault]

@sunny_talwar  could you please take a look if you have a chance.

Thanks

[sunny_talwar · Accepted solution]

Try this

SET NullInterpret = '';

Table:
LOAD * INLINE [
    Id, Date, Balance, Projected Revenue
    1, 1/1/2022, 82500000.00
    1, 2/1/2022, 72187500.00
    1, 3/1/2022, 62875000.01, 1000000.00
    1, 4/1/2022, 51562500.01
    1, 5/1/2022, 41250000.02
    1, 6/1/2022, 30937500.02
    1, 7/1/2022, 20625000.09
    1, 8/1/2022, 10312501.37
];

Table1:
LOAD Id,
	 Date,
	 Balance,
	 [Projected Revenue],
	 If(Id = Previous(Id), RangeSum([Projected Revenue], Peek('Running Projected Revenue')), RangeSum([Projected Revenue])) as [Running Projected Revenue],
	 If(Id = Previous(Id), RangeSum(Balance, [Projected Revenue], Peek('Running Projected Revenue')), RangeSum(Balance, [Projected Revenue])) as [Projected Balance],
	 If(Id = Previous(Id), If(Len(Trim([Projected Revenue])) = 0, Peek('Factor'), [Projected Revenue]/Balance), If(Len(Trim([Projected Revenue])) = 0, 1, [Projected Revenue]/Balance)) as Factor
Resident Table
Order By Id, Date;

DROP Table Table;

[mjperreault]

Perfect thanks

[mjperreault]

Hi @sunny_talwar ,

My data/ need has changed slightly was wondering if you'd be able to help. I have the below Data where 

Balance Original = Prior Balance - Principal

Date	Balance Original	Principal	Projected Funding Amount
9/1/2021 12:00:00 AM	123,750,000.00	-	0
10/1/2021 12:00:00 AM	113,437,500.00	10,312,500.00	0
11/1/2021 12:00:00 AM	103,125,000.01	10,312,499.99	0
12/1/2021 12:00:00 AM	92,812,499.99	10,312,500.01	0
1/1/2022 12:00:00 AM	82,500,000.00	10,312,499.99	0
2/1/2022 12:00:00 AM	72,187,500.00	10,312,500.00	0
3/1/2022 12:00:00 AM	61,875,000.01	10,312,500.00	1,000,000.00
4/1/2022 12:00:00 AM	51,562,500.01	10,312,499.99	0
5/1/2022 12:00:00 AM	41,250,000.02	10,312,499.99	0
6/1/2022 12:00:00 AM	30,937,500.02	10,312,500.01	2,000,000.00
7/1/2022 12:00:00 AM	20,625,000.09	10,312,499.92	0
8/1/2022 12:00:00 AM	10,312,501.37	10,312,498.73	0
8/2/2022 12:00:00 AM	-	10,312,501.37	0

I need to create new columns for "Updated Principal" and "Updated Balance" and "Growth Factor".  

Updated Principal will equal

Principal * (1+(Projected Funding Amount/ Prior Updated Balance)

and my Updated Balance will equal

Prior Balance + Projected Funding Amount - Updated Principal.

Growth Factor will equal 

(1 + (Projected Funding Amount / Prior Updated Balance) * Last Different Growth Factor)

And should persist until there is a new value for Projected Funding Amount

So for example on

3/1 my Updated Principal should be

10,312,500.00 * (1 + (1,000,000/72,187,500.00)) = 10,455,357.14

and my Updated Balance should be 72,187,500 + 1,000,000 - 10,455,357.14 = 62,832,142.857

and my Growth Factor should be (1 + (1,000,000/72,187,500.00)) * 1 = 1.01385281

On each subsequent Date my Principal needs to be increased by the same Growth Factor until there is a new value for Projected Funding Amount 

So on 4/1/ my Updated Principal should be 10,312,499.99 * 1.01385281 = 10,455,357.13

and my Updated Balance should be calculate using my prior Updated Balance and will be

62,382,142.857 - 10,455,357.13 = 52,276,785.72 

then for 5/1 Updated Principal again is 10,312,499.99 * 1.01385281 = 10,455,357.13

and Updated Balance is 

52,276,785.72 - 10,455,357.13 =  ‭‭41,821,428.59‬

So far I have been able to achieve this in Qlik however where I am running into difficulties is when there is on 5/1 when there is a new value for Projected Funding Amount and my Growth Factor changes

On 6/1 my Growth Factor will be (1+(Projected Funding Amount/Prior Update Balance))* Last Different Growth Factor

(1+(2,000,000/ ‭41,821,428.59‬) ) * 1.01385281 = ‭1.06233766230987

my Updated Principal should be 

10,312,500.01 * 1.06233766230987‬ = 10,955,357.15

and my Updated Balance should be 

41,821,428.59‬ + 2,000,000 -  10,955,357.15 = ‭32,866,071.44‬

Ultimately my final table should look like this.  

Date	Balance Original	Principal	Projected Funding Amount	Growth Factor	Updated Principal	Updated Balance
9/1/2021 12:00:00 AM	123,750,000.00	-	0	1	-	123,750,000.00
10/1/2021 12:00:00 AM	113,437,500.00	10,312,500.00	0	1	10,312,500.00	113,437,500.00
11/1/2021 12:00:00 AM	103,125,000.01	10,312,499.99	0	1	10,312,499.99	103,125,000.01
12/1/2021 12:00:00 AM	92,812,499.99	10,312,500.01	0	1	10,312,500.01	92,812,499.99
1/1/2022 12:00:00 AM	82,500,000.00	10,312,499.99	0	1	10,312,499.99	82,500,000.00
2/1/2022 12:00:00 AM	72,187,500.00	10,312,500.00	0	1	10,312,500.00	72,187,500.00
3/1/2022 12:00:00 AM	61,875,000.01	10,312,500.00	1,000,000.00	1.013852814	10,455,357.14	62,732,142.86
4/1/2022 12:00:00 AM	51,562,500.01	10,312,499.99	0	1.013852814	10,455,357.14	52,276,785.73
5/1/2022 12:00:00 AM	41,250,000.02	10,312,499.99	0	1.013852814	10,455,357.13	41,821,428.59
6/1/2022 12:00:00 AM	30,937,500.02	10,312,500.01	2,000,000.00	1.062337662	10,955,357.15	32,866,071.44
7/1/2022 12:00:00 AM	20,625,000.09	10,312,499.92	0	1.062337662	10,955,357.06	21,910,714.38
8/1/2022 12:00:00 AM	10,312,501.37	10,312,498.73	0	1.062337662	10,955,355.79	10,955,358.59
8/2/2022 12:00:00 AM	-	10,312,501.37	0	1.062337662	10,955,358.59	0.00

Apologies if I did not explain well any help is very much appreciated.

[sunny_talwar · Accepted solution]

Try this

SET NullInterpret = '';

Table:
LOAD * INLINE [
    Date, Balance Original, Principal, Projected Funding Amount
    9/1/2021, 123750000.00, , 0.00
    10/1/2021, 113437500.00, 10312500.00, 0.00
    11/1/2021, 103125000.01, 10312499.99, 0.00
    12/1/2021, 92812499.99, 10312500.01, 0.00
    1/1/2022, 82500000.00, 10312499.99, 0.00
    2/1/2022, 72187500.00, 10312500.00, 0.00
    3/1/2022, 61875000.01, 10312500.00, 1000000.00
    4/1/2022, 51562500.01, 10312499.99, 0.00
    5/1/2022, 41250000.02, 10312499.99, 0.00
    6/1/2022, 30937500.02, 10312500.01, 2000000.00
    7/1/2022, 20625000.09, 10312499.92, 0.00
    8/1/2022, 10312501.37, 10312498.73, 0.00
    8/2/2022, , 10312501.37, 0.00
];

Table1:
LOAD Date,
	 [Balance Original],
	 Principal,
	 [Projected Funding Amount],
	 If(Len(Trim(Peek('Running Projected Funding Amount'))) = 0, 1,
	 	If([Projected Funding Amount] = 0, Peek('Running Projected Funding Amount'), (1 + ([Projected Funding Amount]/Peek('Updated Balance')))*Peek('Running Projected Funding Amount'))) as [Running Projected Funding Amount],
	 Principal *
	 If(Len(Trim(Peek('Running Projected Funding Amount'))) = 0, 1,
	 	If([Projected Funding Amount] = 0, Peek('Running Projected Funding Amount'), (1 + ([Projected Funding Amount]/Peek('Updated Balance')))*Peek('Running Projected Funding Amount'))) as [Updated Principal],
	 RangeSum(Alt(Peek('Updated Balance'), [Balance Original]), [Projected Funding Amount],
	 -(Principal *
	 If(Len(Trim(Peek('Running Projected Funding Amount'))) = 0, 1,
	 	If([Projected Funding Amount] = 0, Peek('Running Projected Funding Amount'), (1 + ([Projected Funding Amount]/Peek('Updated Balance')))*Peek('Running Projected Funding Amount'))))) as [Updated Balance]
Resident Table
Order By Date;

DROP Table Table;

[mjperreault]

Works perfectly ! Thanks so much