Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

chenqiyu
New Contributor II

Qlik calculated field

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;

Tags (1)
4 Replies
ramzi-manoubi
Contributor

Re: Qlik calculated field

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.

MVP
MVP

Re: Qlik calculated field

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;




chenqiyu
New Contributor II

Re: Qlik calculated field

Hi Ramzi,

thanks for your help,

It seems use "" or []  or '' are all OK for field names with special characters.

chenqiyu
New Contributor II

Re: Qlik calculated field

Hi Massimo,

thanks for your help! The Proceeding load function is really helpful!