Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
A
T1
Y
G1
2
10
T2
X
3
20
B
30
4
5
40
I need Score1 and score2 for my calculations. Following is my straight table. (refer column heading for expression)
sum(Score2)
sum(Score1)
sum(Score2)-sum(Score1)
(sum(Score2)-sum(Score1))
/sum(Score2)
100
8
92
17
0
27
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
(sum(Score2)-sum(Score1))/sum(Score2)
130
122
Could someone help me fixing the issue?
Thanks