Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

- Qlik Community
- :
- Forums
- :
- Analytics
- :
- New to Qlik Sense
- :
- Re: Sum(A) + Sum(B) or Sum(A+B)

Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

thkarner

Partner - Creator III

2017-09-01
04:20 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Sum(A) + Sum(B) or Sum(A+B)

Hi,

a quick one, where I´m uncertain:

I simply want to sum up two fields inculding (same) set analysis. Normally I do it in this way:

sum( {<[Date.Cal.Year]={2017}>} [FieldA]) + sum( {<[Date.Cal.Year]={2017}>} [FieldB])

Now I saw that this also works:

sum( {<[Date.Cal.Year]={2017}>} ([FieldA] + [FieldB]))

FieldA and FieldB are both from the same (facts) table.

Due to I have a very large data set and complex nested formulas I´d prefer the second version, but I´m uncertain if there could be any differences in the result.

Can anybody confirm whether the two versions are identical or otherwise outline the difference?

Thanks, Thomas

2,731 Views

- « Previous Replies
- Next Replies »

20 Replies

kenphamvn

Creator III

2017-09-01
05:02 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Hi

i think the second solution is bester

but if FieldA and FieldB are both from the same (facts) table you can pre caculate in script load data

Load [FieldA] + [FieldB] as TOTAL

from table

size app will increase but performance is best

1,836 Views

thkarner

Partner - Creator III

2017-09-01
05:19 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Thanks. I know about pre-calculation in the script, which is not applicable in my case for some reasons.

Could you (or anybody else) confirm doubtless if the 2 calculations would lead to same result?

1,836 Views

lakshmikandh

Specialist II

2017-09-01
05:24 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Both has given same results in my app.

lakkydev

Creator II

2017-09-01
05:29 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

if both fields comes from same table the result should be same for both calculations

Not applicable

2017-09-01
05:32 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Hi Thomas,

If both columns have any numeric values then the result will be same. But if there is a possibility that any of the column can have NULL/Non numeric values then I will advise the first method for accuracy.

tresesco

MVP

2017-09-01
05:46 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

I would try to expand on Chandan's comment. To avoid the null/non-numeric issue, you could also try a simpler way using **RangeSum()** like:

sum( {<[Date.Cal.Year]={2017}>} **RangeSum**([FieldA] , [FieldB]))

jonathandienst

Partner - Champion III

2017-09-01
05:47 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

**They will NOT give the same results if there are any null values.**

For Sum(A+B), if A is null, then A+B is also null, so that value of B will not be in the sum result. But it will be in the sum result for Sum(A) + Sum(B). The same applies if B is null and A is not null.

Deciding which is the correct one depends on your requirements. But make the mistake of assuming that they are equivalent.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

rohitk1609

Master

2017-09-01
06:02 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Hi Jonathan,

How it will work, when both facts are from different table ?

1,836 Views

thkarner

Partner - Creator III

2017-09-01
06:38 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Hi Treseco B,

In my case I very large data sets and have ensure performance.

Do you know how is your expression is executed?

sum( {<[Date.Cal.Year]={2017}>}

RangeSum([FieldA] , [FieldB]))

Possible methods I see are:

a) Calculate the RangeSum for ALL (millions) of records and afterwards calculate the Sum with the Set Analysis

OR

b) The main calculation is the Sum considering the records in the Set Analysis only and then calculate the RangeSum for those records

If it´s method be I see this as a resolution also in case of large data sets.

What do you think?

1,836 Views

- « Previous Replies
- Next Replies »