topic Re: rangetotal with aggr in App Development

rangetotal with aggr

MarivdBroek — Thu, 22 Aug 2024 14:15:52 GMT

Good afternoon,

I am new to Qlik Sense.

I have the following table:

HACCP	Type	Date	Mutation	KGS	rangesum	wanted
00001	1 Norm	01-01-2014	-	0	4.535	0
00001	2 Frequency	01-01-2014	-	0	0	0
00001	3 Partij	20-07-2020	MZ200076-2	395	0	395
00001	3 Partij	14-08-2020	MZ200090-2	1.380	0	1.775
00001	3 Partij	30-09-2020	MZ200119-2	1.380	0	3.155
00001	3 Partij	30-09-2020	MZ200120-2	1.380	0	4.535
00010	1 Norm	01-01-2014	-	0	81.094
00010	2 Frequency	01-01-2014	-	0	0	0
00010	3 Partij	07-04-2020	IZ200012 - 1	978	0	978
00010	3 Partij	07-04-2020	IZ200012 - 2	30.000	0	30.978
00010	3 Partij	07-04-2020	IZ200012 - 3	45.000	0	75.978
00010	3 Partij	07-04-2020	IZ200012 - 4	5.116	0	81.094
00019	1 Norm	01-01-2014	-	0	0	0
00019	2 Frequency	01-01-2014	-	0	0	0
00040	1 Norm	01-01-2014	-	0	600	0
00040	2 Frequency	01-01-2014	-	0	0	0
00040	3 Partij	26-11-2020	IZ200109 - 1	600	0	600
01000	1 Norm	01-01-2014	-	0	53.999	0
01000	2 Frequency	01-01-2014	-	0	0	0
01000	3 Partij	03-01-2020	IZ190122 - 1	2.000	0	2.000
01000	3 Partij	03-01-2020	IZ190122 - 2	10.000	0	12.000
01000	3 Partij	03-01-2020	IZ190122 - 3	25.000	0	37.000
01000	3 Partij	03-01-2020	IZ190122 - 4	16.999	0	53.999

In the rangesum I used this formula: num(Rangesum(aggr(sum(KGS), HACCP)), '#.##0')

In the right column 'wanted' you see what I want to see, so the total is correct, but I want the running total.

In the forum there are give a lot of possibilities, but the correct one I did not find.

Re: rangetotal with aggr

hic — Fri, 23 Aug 2024 11:44:41 GMT

You need to explain the logic in the wanted calculation. It is not clear to me what you want to do.

But generally you should not use RangeSum() to aggregate the result of an Aggr(). Use one of the two following:

RangeSum(Above(...))
Sum(Aggr(...))

Above() is a good function to use for running totals. See also https://community.qlik.com/t5/Design/The-Above-Function/ba-p/1465357

Re: rangetotal with aggr

MarivdBroek — Fri, 23 Aug 2024 12:24:29 GMT

I want a running total of KGS per HACCP. The order of the table is: HACCP, Type, Date and Mutation. To make it clearer I added a column rownumbers and total per HACCP,

HACCP	Type	Date	Mutation	KGS	row	rangesum	Wanted running total KGS	total per HACCP
1	1 Norm	1-1-2014	-	0	0	4.535	0	total row 0 kgs (of HACCP 1) so 0
1	2 Frequency	1-1-2014	-	0	1	0	0	total row 0+1 kgs (of HACCP 1) so 0+0
1	3 Partij	20-7-2020	MZ200076-2	395	2	0	395	total row 0+1+2 kgs (of HACCP 1) so 0+0+395
1	3 Partij	14-8-2020	MZ200090-2	1.380	3	0	1.775	total row 0+1+2+3 kgs (of HACCP 1) so 0+0+395+1380
1	3 Partij	30-9-2020	MZ200119-2	1.380	4	0	3.155	total row 0+1+2+3+4 kgs (of HACCP 1) so 0+0+395+1380+1380
1	3 Partij	30-9-2020	MZ200120-2	1.380	5	0	4.535	total of row 0+1+2+3+4+5 kgs (of HACCP 1) so 0+0+395+1380+13801380
10	1 Norm	1-1-2014	-	0	0	81.094		total row 0 kgs (of HACCP 10) so 0
10	2 Frequency	1-1-2014	-	0	1	0	0	total row 0+1 kgs (of HACCP 10) so 0+0
10	3 Partij	7-4-2020	IZ200012 - 1	978	2	0	978	total row 0+1+2 kgs (of HACCP 10) so 0+0+978
10	3 Partij	7-4-2020	IZ200012 - 2	30.000	3	0	30.978	total row 0+1+2+3 kgs (of HACCP 10) so 0+0+978+30000
10	3 Partij	7-4-2020	IZ200012 - 3	45.000	4	0	75.978	total row 0+1+2+3+4 kgs (of HACCP 10) so 0+0+978+30000+45000
10	3 Partij	7-4-2020	IZ200012 - 4	5.116	5	0	81.094	total row 0+1+2+3+4+5 kgs (of HACCP 10) so 0+0+978+30000+45000+5116
19	1 Norm	1-1-2014	-	0	0	0	0
19	2 Frequency	1-1-2014	-	0	1	0	0
40	1 Norm	1-1-2014	-	0	0	600	0	total row 0 kgs (of HACCP 40)
40	2 Frequency	1-1-2014	-	0	1	0	0	total row 0+1 kgs (of HACCP 40) so 0+0
40	3 Partij	26-11-2020	IZ200109 - 1	600	2	0	600	total row 0+1+2 kgs (of HACCP 40) so 0+0+600
1000	1 Norm	1-1-2014	-	0	0	53.999	0	total row 0 kgs (of HACCP 1000) so 0
1000	2 Frequency	1-1-2014	-	0	1	0	0	total row 0+1 kgs (of HACCP 1000) so 0+0
1000	3 Partij	3-1-2020	IZ190122 - 1	2.000	2	0	2.000	total row 0+1+2 kgs (of HACCP 1000) so 0+0+2000
1000	3 Partij	3-1-2020	IZ190122 - 2	10.000	3	0	12.000	total row 0+1+2+3 kgs (of HACCP 1000) so 0+0+2000+10000
1000	3 Partij	3-1-2020	IZ190122 - 3	25.000	4	0	37.000	total row 0+1+2+3+4 kgs (of HACCP 1000) so 0+0+2000+10000+25000
1000	3 Partij	3-1-2020	IZ190122 - 4	16.999	5	0	53.999	total row 0+1+2+3+4+5 kgs (of HACCP 1000) so 0+0+2000+10000+25000+16999

In the actual table I have a few columns more (between Mutation and KGS). To the right of the running total come more columns with different info. It is a table, not a pivot.

Re: rangetotal with aggr

MarivdBroek — Wed, 28 Aug 2024 10:12:09 GMT

It works now. Thank you.