Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
mahnoor1279
Contributor III
Contributor III

How to sum values till the value exsist in previous column

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
Labels (5)
1 Solution

Accepted Solutions
udit_k
Partner - Creator II
Partner - Creator II

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)

udit_k_0-1689240461213.png

 

 

View solution in original post

1 Reply
udit_k
Partner - Creator II
Partner - Creator II

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)

udit_k_0-1689240461213.png