Sum() in load script

[govindvasu]

Hi,

I am trying to implement sum functionality in the data load editor, when loading the data it is throwing the invalid expression error. 

Here is the script i am running

Data_total:

load

     emp_id,

     key,

    amount,

     sum(amount) as Sum_amount

resident Data

where Key = 0

and Date >= addmonths(today(), -12)

group by emp_id;

[BrunPierre]

Hi, You need to include all fields within the GROUP BY clause that are not aggregated or another way could be this.

Data_total:
LOAD
emp_id,
Only(key) as key,
Only(amount) as amount,
sum(amount) as Sum_amount
RESIDENT Data
WHERE Key = 0 AND Date >= Date(addmonths(today(), -12),'YourFormat')
GROUP BY emp_id;

[cristianj23a]

try this code.

Where Year(Date)>'2017';

Don't forget that your "Date" field is in 'DD/MM/YYYY' format, you must change it to Year so you can filter.

[govindvasu]

Tried this still showing as invalid expression. I Just tried only the sum() ,groupby clause on emp_id still showing as invalid expression. Is there any specific article for implementing the Sum()?
Appreciate your help !

[govindvasu]

Tried this still throwing invalid expression. I think i am doing something wrong in the sum function

appreciate your help!

[cristianj23a]

Please send me the full code you are using and a screenshot of the error message.
To be able to help you.

[govindvasu]

Here is the code of data load editor in qliksense,

Temp:

LOAD

    ID,

    Name,

    "Date",

    "Money(Amount)"

FROM [Book1.xlsx]

(ooxml, embedded labels, table is Sheet2);

Sum:

LOAD Name, 

Sum("Money(Amount)") as MySum 

Resident Temp 

Where Year("Date")>2017

Group By  ID;

Here is the screenshot of the error

[cristianj23a]

Hol, it happens that in your GROUP BY you are putting the ID field, when in your load it does not exist.

Temp:
LOAD
ID,
Yam,
"Give yourself",
"Money(Amount)"

FROM [Book1.xlsx]
(ooxml, embedded labels, table is Sheet2);

sum:
LOAD ID, //Replace the ID
Sum("Money(Amount)") as MySum
Resident Temp
Where Year("Date")>2017
Group By ID;

[govindvasu]

Updated my Load statement, still the same.

is this something related to type of fields?
I am doubting the field " Money(Amount)" sample of data in field " Money(Amount)" is below

$500.00

$200.00

$200.00

$250.00

[cristianj23a · Accepted solution]

It happens that you are adding the "Name" field, if you want to use the Name then add it to the GROUP BY.

Temp:
LOAD
ID,
Name,
"Give yourself",
"Money(Amount)"

FROM [Book1.xlsx]
(ooxml, embedded labels, table is Sheet2);

sum:
LOAD ID, //Replace the ID
Name,
Sum("Money(Amount)") as MySum
Resident Temp
Where Year("Date")>2017
Group By ID, Name;

About the Money field, it's better to remove $ and format it in the chart.