topic Re: Running Totals using Peek in App Development

Running Totals using Peek

mjperreault — Sat, 16 Nov 2024 05:02:41 GMT

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

Re: Running Totals using Peek

mjperreault — Wed, 07 Aug 2019 21:25:24 GMT

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

Thanks

Re: Running Totals using Peek

sunny_talwar — Thu, 08 Aug 2019 11:43:39 GMT

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;

Re: Running Totals using Peek

mjperreault — Thu, 08 Aug 2019 15:09:11 GMT

Perfect thanks

Re: Running Totals using Peek

mjperreault — Fri, 09 Aug 2019 21:40:19 GMT

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.

Re: Running Totals using Peek

sunny_talwar — Mon, 12 Aug 2019 11:54:52 GMT

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;

Re: Running Totals using Peek

mjperreault — Mon, 12 Aug 2019 17:31:03 GMT

Works perfectly ! Thanks so much