topic Re: Using Sum in Script in QlikView

Using Sum in Script

Tue, 22 Dec 2015 22:20:37 GMT

HI All,

I have a table as below:

LOAD [SALES ID],

[Paid Cash],

[Paid Card],

[On Account],

[Paid Cheque],

[Paid In Account]

Resident Temp2;

I need to create a calculated field in there which does Sum([Paid Cash] + [Paid Card] + [On Account] + [Paid Cheque] +[Paid In Account]) as [Total Paid]

Can anyone suggest how to do this?

Thanks,

Re: Using Sum in Script

Anonymous — Tue, 22 Dec 2015 23:57:33 GMT

Maybe,

TMP:

LOAD [SALES ID],

[Paid Cash],

[Paid Card],

[On Account],

[Paid Cheque],

[Paid In Account]

Resident Temp2;

Sales:

Load

[SALES ID]

Sum([Paid Cash] + [Paid Card] + [On Account] + [Paid Cheque] +[Paid In Account]) as [Total Paid]

Resident TMP

Group by [SALES ID];

regards

Re: Using Sum in Script

Peter_Cammaert — Wed, 23 Dec 2015 00:13:58 GMT

The solution by Jannet is probably the best you can get, because there is no other discerning field for identifying individual records.

If on the other hand, you want to keep just the sum of the individual fields (in QlikView terms, there is no difference at all between the two solutions if you aggregate by [SALES ID] except when processing huge numbers os rows = script execution takes longer, UI responses come quicker), you can just throw the various fields together row-by-row, like in:

LOAD [SALES ID],

[Paid Cash],

[Paid Card],

[On Account],

[Paid Cheque],

[Paid In Account],

[Paid Cash] + [Paid Card] + [On Account] + [Paid Cheque] +[Paid In Account]) as [Total Paid]

Resident Temp2;

Sum([Total Paid]) will produce the same result in both cases.

Best,

Peter

PS Use RangeSum() if you want to avoid getting a NULL value as total if any single field contains Null.

Re: Using Sum in Script

jagan — Wed, 23 Dec 2015 01:14:03 GMT

HI,

Try like this using Rangesum() like below,

LOAD [SALES ID],

[Paid Cash],

[Paid Card],

[On Account],

[Paid Cheque],

[Paid In Account],

Rangesum([Paid Cash], [Paid Card] , [On Account] , [Paid Cheque] , [Paid In Account]) as [Total Paid]

Resident Temp2;

Hope this helps you.

Regards,

jagan.

Re: Using Sum in Script

Thu, 24 Dec 2015 00:22:14 GMT

Everyone thanks for your help.

Jagan this works fine.

Regards,

Re: Using Sum in Script

jagan — Fri, 01 Jan 2016 15:22:44 GMT

Hi,

If you got the answer please close this thread by giving Correct Answer to the post which helps you in getting the answer..

Regards,

Jagan.