topic Re: Cumulative Sum in Script in QlikView

Cumulative Sum in Script

AleRods88 — Wed, 25 Nov 2020 16:16:04 GMT

Hello,

I have the following table:

ID	DATE	TIME	TIME_SORT	VALUE
1	1/1/2019	6	1	1
1	1/1/2019	7	2	17
1	1/1/2019	8	3	5
1	1/1/2019	9	4	5
1	1/1/2019	10	5	7
1	1/1/2019	11	6	13
1	1/1/2019	12	7	0
1	1/1/2019	13	8	3
1	1/1/2019	14	9	8
1	1/1/2019	15	10	22
1	1/1/2019	16	11	23
1	1/1/2019	17	12	38
1	1/1/2019	18	13	31
1	1/1/2019	19	14	38
1	1/1/2019	20	15	30
1	1/1/2019	21	16	46
1	1/1/2019	22	17	46
1	1/1/2019	23	18	15
2	5/1/2019	1	2	5
2	5/1/2019	1	2	10

What I need is to add a colum named "cumulative" that makes a cumulative by ID and Date:

ID	DATE	TIME	TIME_SORT	VALUE	CUMULATIVE
1	1/1/2019	6	1	1	1
1	1/1/2019	7	2	17	18
1	1/1/2019	8	3	5	23
1	1/1/2019	9	4	5	28
1	1/1/2019	10	5	7	35
1	1/1/2019	11	6	13	48
1	1/1/2019	12	7	0	48
1	1/1/2019	13	8	3	51
1	1/1/2019	14	9	8	59
1	1/1/2019	15	10	22	81
1	1/1/2019	16	11	23	104
1	1/1/2019	17	12	38	142
1	1/1/2019	18	13	31	173
1	1/1/2019	19	14	38	211
1	1/1/2019	20	15	30	241
1	1/1/2019	21	16	46	287
1	1/1/2019	22	17	46	333
1	1/1/2019	23	18	15	348
2	5/1/2019	1	2	5	5
2	5/1/2019	1	2	10	15

Is there any way to do that?

Thank you!!!

Re: Cumulative Sum in Script

_ylc — Thu, 19 Mar 2020 19:44:36 GMT

Hi,

What you can do is create 2 keys.

1 for sorting and 1 for lookup

key1 will be ID,DATE, TIME and TIME_SORT

key2 will be ID and DATE

temp:
LOAD *,
ID & '_' & DATE&'_'& TIME & '_'* TIME_SORT as key1,

ID & '_' & DATE as key2
;
LOAD * INLINE [
ID, DATE, TIME, TIME_SORT, VALUE
1, 1/1/2019, 6, 1 ,1
1, 1/1/2019, 7 ,2 ,17
1, 1/1/2019, 8 ,3 ,5
1, 1/1/2019, 9 ,4 ,5
1, 1/1/2019, 10 ,5 ,7
1, 1/1/2019, 11 ,6 ,13
1, 1/1/2019, 12 ,7 ,0
1, 1/1/2019, 13 ,8 ,3
1, 1/1/2019, 14 ,9 ,8
1, 1/1/2019, 15 ,10 ,22
1, 1/1/2019, 16 ,11 ,23
1, 1/1/2019, 17 ,12 ,38
1, 1/1/2019, 18 ,13 ,31
1, 1/1/2019, 19 ,14 ,38
1, 1/1/2019, 20 ,15 ,30
1, 1/1/2019, 21 ,16 ,46
1, 1/1/2019, 22 ,17 ,46
1, 1/1/2019, 23 ,18 ,15
2, 5/1/2019, 1 ,2 ,5
2, 5/1/2019, 1 ,2 ,10
];

//Create a resident table that will order by your dataset using the key1.

//Then use the previous or peek function to compare the key2 if they are equal, if yes, then you should add the value. //if no then just get the value

final:
LOAD *,
if(peek(key2,-1)=key2, peek(CUMULATIVE,-1)+VALUE, VALUE) as CUMULATIVE//if(Previous(%key)=%key, previous(VALUE) + VALUE, VALUE) as testvalue,
;
LOAD * resident temp
order by key1 asc;

drop table temp;

Re: Cumulative Sum in Script

Brett_Bleess — Tue, 14 Apr 2020 17:47:24 GMT

If the partners suggested solution worked for you, please be sure to return to your thread to properly close it out by using the Accept as Solution button on the partner's post to give them credit and let other Members know that worked. If you require further assistance, please leave an update post. We appreciate it if you do close out your threads, and if you did something different, you can post that and then use the button to mark that as the solution too.

Check out the following Design Blog post if you are still working upon things, it may be of some further help too:

https://community.qlik.com/t5/Qlik-Design-Blog/Accumulations/ba-p/1466581

Regards,
Brett