Field calculations

[AmCh]

Hi all,

I have the following field and table as an example:

Year

1

2

3

System	Field_a	Field_b
A	1	7
A	2	8
B	3	9
B	4	10
B	5	11
C	6	12

Desired output:

System	Year	Sum(Field_a*Field_b)*Year
A	1	23=(7*1+8*2)*1
A	2	46=(7*1+8*2)*2
A	3	69=(7*1+8*2)*3
B	1	122=(9*3+4*10+5*11)*1
B	2	244=(9*3+4*10+5*11)*2
B	3	366=(9*3+4*10+5*11)*3
C	1	..
...	...	..
Total	1	Sum all systems for year 1
Total	2	Sum all systems for year 2
Total	3	Sum all systems for year 2

Any help please?

Thanks in advance.

[Vegar]

Please explain how you get (7+16) and (27+40) and please do also complete the desired output for all the rows of System B.

[AmCh]

done!

[Vegar]

You are totally ignoring the third row of B-values? Is that an correct observation?

System	Field_a	Field_b
A	1	7
A	2	8
B	3	9
B	4	10
B	5	11
C	6	12

[AmCh]

Ah sorry, I corrected it. The sum should performed for all rows each system.

[Vegar · Accepted solution]

You you need to solve this in script or in application objects?

In application solution

Years:
LOAD * Inline [
Year
1
2
3
];

Transactions:
LOAD * inline [
System, Field_a, Field_b
A, 1, 7
A, 2, 8
B, 3, 9
B, 4, 10
B, 5, 11
C, 6, 12
];

Create an object with the two dimensions and the following expression:

sum(Field_a*Field_b)*Year

[AmCh]

In the script please. Later I need the field Sum(Field_a*Field_b)*Year  as chart expression, Year as a dimension and System on the dashboard to filter.

[Vegar · Accepted solution]

In script solution
TMP_Transactions:
LOAD System, sum(Field_a * Field_b) as Field_ab inline [
System, Field_a, Field_b
A, 1, 7
A, 2, 8
B, 3, 9
B, 4, 10
B, 5, 11
C, 6, 12
]
Group by System

;
JOIN
LOAD * Inline [
Year
1
2
3];

LOAD
System,
Year,
Field_ab*Year as Amount
Resident TMP_Transactions;

[AmCh]

Thanks Vegar, nice job!

B.t.w. can you help also with this topic please?

https://community.qlik.com/t5/QlikView-App-Dev/Import-Excel/m-p/1806414#M1212149

Thanks in advance!