How to sum values till the value exsist in previous column

[mahnoor1279]

Hello everyone,

I've below data where i have TOTAL_AMOUNT on 13th april and I want only the sum of VALUES till the 13th april,

this will be apply for all the same months.

YEAR([DATE])	month([DATE])	day([DATE])	TOTAL_AMOUNT	SUM(VALUE)
2023	Apr	1	-	118.421
2023	Apr	2	-	118.4806
2023	Apr	3	-	118.5521
2023	Apr	4	-	118.5873
2023	Apr	5	-	118.6362
2023	Apr	6	-	118.6714
2023	Apr	7	-	118.7339
2023	Apr	8	-	118.7954
2023	Apr	9	-	118.8569
2023	Apr	10	-	118.9158
2023	Apr	11	-	118.9772
2023	Apr	12	-	119.0422
2023	Apr	13	2427276427	119.0826
2023	Apr	14	-	119.1671
2023	Apr	15	-	119.2299
2023	Apr	16	-	119.2927
2023	Apr	17	-	119.3698
2023	Apr	18	-	119.4207
2023	Apr	19	-	119.4825
2023	Apr	20	-	119.5433
2023	Apr	21	-	119.6057
2023	Apr	22	-	119.668
2023	Apr	23	-	119.7303
2023	Apr	24	-	119.7926
2023	Apr	25	-	119.8548
2023	Apr	26	-	119.8896
2023	Apr	27	-	119.9451
2023	Apr	28	-	120.0097
2023	Apr	29	-	120.074
2023	Apr	30	-	120.1383

[udit_k · Accepted solution]

We can create day1 field in load script as below:-

load *,
if(len(TOTAL_AMOUNT)>0,day) as day1;
LOAD * INLINE [

YEAR, month, day, TOTAL_AMOUNT, VALUE
2023, Apr, 1,, 118.421
2023, Apr, 2,, 118.4806
2023, Apr, 3,, 118.5521
2023, Apr, 4,, 118.5873
2023, Apr, 5,, 118.6362
2023, Apr, 6,, 118.6714
2023, Apr, 7,, 118.7339
2023, Apr, 8,, 118.7954
2023, Apr, 9,, 118.8569
2023, Apr, 10,, 118.9158
2023, Apr, 11,, 118.9772
2023, Apr, 12,, 119.0422
2023, Apr, 13, 2427276427, 119.0826
2023, Apr, 14,, 119.1671
2023, Apr, 15,, 119.2299
2023, Apr, 16,, 119.2927
2023, Apr, 17,, 119.3698
2023, Apr, 18,, 119.4207
2023, Apr, 19,, 119.4825
2023, Apr, 20,, 119.5433
2023, Apr, 21,, 119.6057
2023, Apr, 22,, 119.668
2023, Apr, 23,, 119.7303
2023, Apr, 24,, 119.7926
2023, Apr, 25,, 119.8548
2023, Apr, 26,, 119.8896
2023, Apr, 27,, 119.9451
2023, Apr, 28,, 120.0097
2023, Apr, 29,, 120.074
2023, Apr, 30,, 120.1383

];

use below exp in chart :-

Sum({<day={">=$(=min(day))<=$(=max(day1))"}>}VALUE)