Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Guys,
I am new to Qlikview/ Sense, I am trying to create a calculated field margin_per_unit (1st calculated field) and use that field to calculate the profit (2nd calculated field).
However, it seems below script does not work. When create a KPI object, I expect the sum(profit) = (18-15)*10 + (7-5)*2 = 34. but the app shows as 0.
Can you please assist?
thanks.
++++++++++++++++++++++++++
test:
load *inline
[
cost,quantity,price
15,10,18
5,2,7
];
test2:
load
cost,
quantity,
price,
price - cost as 'margin_per_unit',
'margin_per_unit' * quantity as profit
resident test;
drop table test;
Hello. Following should work:
test2:
load
cost,
quantity,
price,
price - cost as margin_per_unit,
(price - cost) * quantity as profit
resident test;
Also instead of single quotes,
use double quote or square brackets when you have a space or a special character in a field name.
you can't use an alias (bold) in the same load
so this doesn't work
test2:
load
cost,
quantity,
price,
price - cost as margin_per_unit,
margin_per_unit * quantity as profit
resident test;
you should repeat (price - cost) in the 2nd calculated field
..........
price - cost as margin_per_unit,
(price - cost) * quantity as profit
.............
another option, useful for many and/or comlex calculated fields is to use a preceding load Preceding Load
load
*,
margin_per_unit * quantity as profit;
load
cost,
quantity,
price,
price - cost as margin_per_unit
resident test;
Hi Ramzi,
thanks for your help,
It seems use "" or [] or '' are all OK for field names with special characters.
Hi Massimo,
thanks for your help! The Proceeding load function is really helpful!