topic Re: Sum in Script in App Development

Sum in Script

etrotter — Tue, 31 Jul 2018 09:34:43 GMT

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.

Re: Sum in Script

Chanty4u — Tue, 31 Jul 2018 09:55:35 GMT

did you tried without num and reload?

Re: Sum in Script

Chanty4u — Tue, 31 Jul 2018 09:56:41 GMT

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

Re: Sum in Script

etrotter — Tue, 31 Jul 2018 10:07:51 GMT

I tried both and still recieving the same error message.

Re: Sum in Script

Chanty4u — Tue, 31 Jul 2018 10:09:39 GMT

can you paste your full script ?

Re: Sum in Script

BalaBhaskar_Qlik — Tue, 31 Jul 2018 12:19:47 GMT

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.

Re: Sum in Script

etrotter — Thu, 02 Aug 2018 10:38:32 GMT

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;

Re: Sum in Script

BalaBhaskar_Qlik — Thu, 02 Aug 2018 10:48:21 GMT

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;

Re: Sum in Script

mhappiee — Thu, 02 Aug 2018 12:03:14 GMT

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