Skip to main content

App Development

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

Announcements
Welcome to Qlik Community! Check out our new navigation! FIND OUT MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
etrotter
Creator II
Creator 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
Chanty4u
MVP
MVP

did you tried without num and reload?

Chanty4u
MVP
MVP

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
Creator II
Creator II
Author

I tried both and still recieving the same error message.

Chanty4u
MVP
MVP

can you paste your full script ?

balabhaskarqlik

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
Creator II
Creator II
Author

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

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
Partner - Contributor III
Partner - Contributor III

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