Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

etrotter
Contributor II

Sum in Script

Hello,

I am having trouble with this expression:

Temp_Sum:

Load

[%CSLGranteeExternalID],

[CSL Account Name],

Sum(Num([CSL Total Annual Revenue])) as [CSL Sum Total Revenue]

Resident Account

Group By [%CSLGranteeExternalID],[CSL Account Name];

It will not load, please let me know what you think could be the problem. I keep getting the error "Invalid Expression" when I run the script. 

8 Replies
sureshqv
Esteemed Contributor III

Re: Sum in Script

did you tried without num and reload?

sureshqv
Esteemed Contributor III

Re: Sum in Script

try

Temp_Sum:

NoConcatenate

Load

[%CSLGranteeExternalID],

[CSL Account Name],

Sum([CSL Total Annual Revenue]) as [CSL Sum Total Revenue]

Resident Account

Group By [%CSLGranteeExternalID],[CSL Account Name];

etrotter
Contributor II

Re: Sum in Script

I tried both and still recieving the same error message.

sureshqv
Esteemed Contributor III

Re: Sum in Script

can you paste your full script ?

balabhaskarqlik
Honored Contributor

Re: Sum in Script

May be can you try like this:

Account:

Load

[%CSLGranteeExternalID],

[CSL Account Name],

[CSL Total Annual Revenue],

...

FROM ABC;

NoConcatenate

Temp_Sum:

Load

[%CSLGranteeExternalID],

[CSL Account Name],

Num(Sum([CSL Total Annual Revenue]),'##,#.00') as [CSL Sum Total Revenue]

Resident Account

Group By [%CSLGranteeExternalID];

Drop table Account;

You could check all the field names of Temp_Sum, matched correctly with Account table.

Or else, could you provide some more script to identify the issue.

etrotter
Contributor II

Re: Sum in Script

Hi, I actually ended up changing the script quite a bit, here is the new script I'm working on, but still haven't been able to get it working:


AllOrgs:

Load

"Grant ID",

%CSLGrantorExternalID,

[CSL Account ID],

[%CSLGranteeExternalID],

//[CSL Account Name],

if([CSL Rev by Funder]>20000,[CSL Account Name], 'All Other Grantees') as [CSL Account Name],

[CSL Account Type],

[CSL Zip],

[CSL Website],

[CSL Total Annual Revenue],

[CSL Total Annual Regranting],

[CSL Org Mission],

[CSL Year of Data],

[CSL No of Employees],

[RCF as Funder];

Load *,

Sum([CSL Total Annual Revenue]) as [CSL Rev by Funder]

Resident AllOrgs_TempGrantee

Group by %CSLGrantorExternalID;


Drop table AllOrgs_TempGrantee;

balabhaskarqlik
Honored Contributor

Re: Sum in Script

Resident AllOrgs_TempGrantee


Where is this: AllOrgs_TempGrantee table?


May be you can try like this:


AllOrgs:

Load

"Grant ID",

%CSLGrantorExternalID,

[CSL Account ID],

[%CSLGranteeExternalID],

//[CSL Account Name],

if([CSL Rev by Funder]>20000,[CSL Account Name], 'All Other Grantees') as [CSL Account Name],

[CSL Account Type],

[CSL Zip],

[CSL Website],

[CSL Total Annual Revenue],

[CSL Total Annual Regranting],

[CSL Org Mission],

[CSL Year of Data],

[CSL No of Employees],

[RCF] as Funder;


NoConcatenate


AllOrgs_TempGrantee

Load *,

Sum([CSL Total Annual Revenue]) as [CSL Rev by Funder]

Resident AllOrgs

Group by %CSLGrantorExternalID,[CSL Account Type];


Drop table AllOrgs;


mhappiee
New Contributor III

Re: Sum in Script

Hi Erica,

Try this 3 and see if that helps:

-try adding all fields in the group by except the aggregating field and then do a resident load of that in a different table

- Try converting the format of the measure in 1st table check in UI and then try aggregate it by resident load .

-try other aggregate functions with 1 field like max and continue adding other fields if it works then you can replace it by Sum

Anand

Community Browser