Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us to spark ideas for how to put the latest capabilities into action. Register here!
cancel
Showing results for 
Search instead for 
Did you mean: 
sureshbaabu
Creator III
Creator III

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

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

  1. 0.92

1

10

2

8

  1. 0.8

2

20

3

17

  1. 0.85

3

30

0

30

1

4

30

3

27

  1. 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

  1. 4.55

1

10

2

8

  1. 0.8

2

20

3

17

  1. 0.85

3

30

0

30

1

4

30

3

27

  1. 0.9

5

40

0

40

1

Could someone help me fixing the issue?

Thanks

0 Replies