Using Sum in Script

[#1]

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,

[Anonymous]

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

[Peter_Cammaert]

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.

[jagan · Accepted solution]

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.

[#5]

Everyone thanks for your help.

Jagan this works fine.

Regards,

[jagan]

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.