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
