Cumulative field with recursive function in script

[igorgois_]

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
];

[MayilVahanan · Accepted solution]

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:

[igorgois_]

It worked!!

Thank you so much, @MayilVahanan 

#lifesaver #legend