topic Re: Field calculations in QlikView

Field calculations

AmCh — Mon, 10 May 2021 08:32:29 GMT

Hi all,

I have the following field and table as an example:

Year

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_aField_b)Year*
A	1	23=(71+82)*1
A	2	46=(71+82)*2
A	3	69=(71+82)*3
B	1	122=(93+410+511)1
B	2	244=(93+410+511)2
B	3	366=(93+410+511)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.

Re: Field calculations

Vegar — Mon, 10 May 2021 04:48:29 GMT

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.

Re: Field calculations

AmCh — Mon, 10 May 2021 08:23:48 GMT

done!

Re: Field calculations

Vegar — Mon, 10 May 2021 08:29:19 GMT

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

Re: Field calculations

AmCh — Mon, 10 May 2021 08:33:48 GMT

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

Re: Field calculations

Vegar — Mon, 10 May 2021 08:41:06 GMT

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

Re: Field calculations

AmCh — Mon, 10 May 2021 08:41:05 GMT

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.

Re: Field calculations

Vegar — Mon, 10 May 2021 08:46:35 GMT

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;

Re: Field calculations

AmCh — Mon, 10 May 2021 08:59:59 GMT

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!