Qlik Community

Qlik Sense App Development

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

Announcements
Uploads getting stuck in the virus scanner. We are investigating.
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted
Champion III
Champion III

did you tried without num and reload?

Highlighted
Champion III
Champion III

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];

Highlighted
Creator II
Creator II

I tried both and still recieving the same error message.

Highlighted
Champion III
Champion III

can you paste your full script ?

Highlighted

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.

Highlighted
Creator II
Creator II

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;

Highlighted

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;


Highlighted
Partner
Partner

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