Total Mode – ‘sum of Rows’ on a straight table

sureshbaabu — Mon, 18 Aug 2014 11:05:16 GMT

Hello,

I’m having an issue with the Total Mode – ‘sum of Rows’ on a straight table.

The following is my scenario

Columns on Table 1 : Name, task_no, Type, [S-type], Group, [S-group], Score1

Columns on Table 2 : Type, [S-type], Group, [S-group], Score2

I have to join using the 4 common column on these tables. So I created a KEY as below

AutoNumber(Type&'|'&[S-type]&'|'&Group&'|'&[S-group]) as KEY,

My script page is as below:

LOAD
AutoNumber(Type&'|'&[S-type]&'|'&Group&'|'&[S-group]) as KEY,
Name,
task_no,
Type,
[S-type],
Group,
[S-group],
Score1

FROM
[Data.xlsx]
(ooxml, embedded labels, table is D1);

LOAD
AutoNumber(Type&'|'&[S-type]&'|'&Group&'|'&[S-group]) as KEY,
//Type,
       // [S-type],
       // Group,
      // [S-group],
      Score2
FROM
[Data.xlsx]
(ooxml, embedded labels, table is D2);

On joining the tables using the ‘KEY’ column. I’m able to use Score2 from Table2

Following is the final data table:

Row number 3 and 4 has the same key but for different task_no.

KEY	Name	task_no	Type	S-type	Group	S-group	Score1	Score2
1	ABC	1	A	T1	Y	G1	2	10
2	ABC	2	A	T2	X	G1	3	20
3	ABC	3	B	T1	X	G1		30
3	ABC	4	B	T1	X	G1	3	30
4	ABC	5	B	T1	Y	G1		40

I need Score1 and score2 for my calculations. Following is my straight table. (refer column heading for expression)

task_no	sum(Score2)	sum(Score1)	sum(Score2)-sum(Score1)	(sum(Score2)-sum(Score1)) /sum(Score2)
	100	8	92	0.92
1	10	2	8	0.8
2	20	3	17	0.85
3	30	0	30	1
4	30	3	27	0.9
5	40	0	40	1

Since the total of Rows on above table are incorrect. I changed the total mode from ‘Expression total’ to ‘sum of Rows’

But the result is incorrect for the last expression (sum(Score2)-sum(Score1)) / sum(Score2). The total should be .94

I tried with sum of rows & average of rows , both resulted incorrect values 4.55 & 0.91 respectively

task_no	sum(Score2)	sum(Score1)	sum(Score2)-sum(Score1)	(sum(Score2)-sum(Score1))/sum(Score2)
	130	8	122	4.55
1	10	2	8	0.8
2	20	3	17	0.85
3	30	0	30	1
4	30	3	27	0.9
5	40	0	40	1

Could someone help me fixing the issue?

Thanks

topic Total Mode – ‘sum of Rows’ on a straight table in QlikView

Total Mode – ‘sum of Rows’ on a straight table