topic Cumulative field with recursive function in script in App Development

Cumulative field with recursive function in script

igorgois_ — Tue, 30 Nov 2021 18:17:33 GMT

I am trying to calculate a field in script that depends on its cumulative field.

For each date I receive the target value of the month. And I need to get the target of the day based on how many working days are remaining and based on the cumulative target values from previous days.

I already tried using peek and previous functions and also with AsOf calendar but without success.

The excel file attached has the logic and results expected and the script below has the input data.

It is like: Column 'C' depends on 'A'. 'A' depends on 'B' and 'B' depends on 'C'.

Does anyone can help me?

Thanks in advance

load * inline [
date,working_days,month_target
01/05/2021,22,0
02/05/2021,22,0
03/05/2021,21,502917
04/05/2021,20,487102
05/05/2021,19,485568
06/05/2021,18,485568
07/05/2021,17,485568
08/05/2021,17,0
09/05/2021,17,0
10/05/2021,16,485568
11/05/2021,15,473965
12/05/2021,14,468302
13/05/2021,13,468302
14/05/2021,12,468302
15/05/2021,12,0
16/05/2021,12,0
17/05/2021,11,468302
18/05/2021,10,469202
19/05/2021,9,456070
20/05/2021,8,456070
21/05/2021,7,456070
22/05/2021,7,0
23/05/2021,7,0
24/05/2021,6,456070
];

Re: Cumulative field with recursive function in script

MayilVahanan — Thu, 27 May 2021 01:33:09 GMT

HI @igorgois_

Try like below

LOAD Date(Date) as Date,
Num([Remaining working days]) as [Remaining working days],
[Month target],
Alt(Peek('Final Accum'),0)+(If([Month target] = 0, 0, [Month target] - Peek('Final Accum')) / [Remaining working days]) as [Final Accum],
If([Month target] = 0, 0, [Month target] - Peek('Final Accum')) / [Remaining working days] as FinalColumn,
If([Month target] = 0, 0, [Month target] - Peek('Final Accum')) as [Month target Remaining]

FROM
[Cumulative recursive.xlsx]
(ooxml, embedded labels, header is 2 lines, table is [Target by day]);

o/p:

Re: Cumulative field with recursive function in script

igorgois_ — Thu, 27 May 2021 12:56:48 GMT

It worked!!

Thank you so much, @MayilVahanan

#lifesaver #legend