Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

Using Sum in Script

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,

1 Solution

Accepted Solutions
Highlighted
MVP & Luminary
MVP & Luminary

Re: Using Sum in Script

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.

View solution in original post

5 Replies
Highlighted
Specialist
Specialist

Re: Using Sum in Script

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

Highlighted

Re: Using Sum in Script

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.

Highlighted
MVP & Luminary
MVP & Luminary

Re: Using Sum in Script

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.

View solution in original post

Highlighted
Not applicable

Re: Using Sum in Script

Everyone thanks for your help.

Jagan this works fine.

Regards,

Highlighted
MVP & Luminary
MVP & Luminary

Re: Using Sum in Script

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.