Adding calculated columns with data load editor

[#1]

Hello,

I use Qlik Sense and try to add a calculated column to my data.

I use data load editor.

The script in auto-generated section is:

LOAD

    Product,

    "Date",

    Amount

FROM [lib://QilkViewData/Book1.xlsx]

(ooxml, embedded labels, table is Sheet1);

I append to the Main section:

LOAD

    Month("Date") as vMonth

FROM [lib://QilkViewData/Book1.xlsx]

(ooxml, embedded labels, table is Sheet1);

then I reload my data.

It gives me vMonth column but when I plot bar chart sum(Amount) vs vMonth, it gives me constant values of sum(Amount) for all the data (see attached project). How do I correct it?

Also, when I try to add a column with complex formula (e.g. sum({<Product = {"A"}>}Amount as vACost), it gives me errors. How do I add a such a column to my data?

[awhitfield]

Hi Egor,

can you also upload your xlsx too please?

Andy

[jonathandienst]

vMonth is not associated in any way with the table containing Amount, so if you use vMonth as a dimension you will always get total value.

[jonathandienst]

sum({<Product = {"A"}>}Amount as vACost) is not a valid expression.

In script, you cannot use set analysis. In an expression, you should write

sum({<Product = {"A"}>}Amount)

and name it vACost

[#5]

Andrew, ok see attachement.

[#6]

Jonathan,

how do I associate it with my data?

[awhitfield · Accepted solution]

HI Egor,

checkout the attached

HTH - Andy

[#8]

Jonathan,

thank you for your answer,

sorry I mistyped, I meant sum({<Product = {"A"}>}Amount) as vACost

ok how do I use an expression sum({<Product = {"A"}>}Amount) for further calculations?

e.g.

ACost = sum({<Product = {"A"}>}Amount)

currentACost = sum({<Date = {"=Today()"}ACost)

[#9]

Andrew,

thank you! That's the correct answer.

But how did you delete the auto-generated section?

[#10]

I am using older version of Qlik and not able to open the file. I'd like to see the example here, can anyone help? TIA.