Skip to main content
Announcements
April 9th: The AI Roadmap: 6 Landmarks for AI-ready Data and Analytics: REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
AmCh
Creator

Field calculations

Hi all,

I have the following field and table as an example:

Year
1
2
3

 

SystemField_aField_b
A17
A28
B39
B410
B511
C612

 

Desired output:

SystemYearSum(Field_a*Field_b)*Year
A123=(7*1+8*2)*1
A246=(7*1+8*2)*2
A369=(7*1+8*2)*3
B1122=(9*3+4*10+5*11)*1
B2244=(9*3+4*10+5*11)*2
B3366=(9*3+4*10+5*11)*3
C1..
........
Total1Sum all systems for year 1
Total2Sum all systems for year 2
Total3Sum all systems for year 2

 

Any help please?


Thanks in advance.

2 Solutions

Accepted Solutions
Vegar
MVP

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

Vegar_0-1620635981660.png

 

View solution in original post

Vegar
MVP

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;

Vegar_1-1620636328628.png

 

View solution in original post

8 Replies
Vegar
MVP

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
Creator
Author

done!

Vegar
MVP

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

SystemField_aField_b
A17
A28
B39
B410
B511
C612
AmCh
Creator
Author

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

Vegar
MVP

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

Vegar_0-1620635981660.png

 

AmCh
Creator
Author

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
MVP

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;

Vegar_1-1620636328628.png

 

AmCh
Creator
Author

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!