Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
Hi Egor,
can you also upload your xlsx too please?
Andy
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.
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
Andrew, ok see attachement.
Jonathan,
how do I associate it with my data?
HI Egor,
checkout the attached
HTH - Andy
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)
Andrew,
thank you! That's the correct answer.
But how did you delete the auto-generated section?
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.