
Re: Sum(A) + Sum(B) or Sum(A+B)
An Pham Sep 1, 2017 5:02 AM (in response to Thomas Karner )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

Re: Sum(A) + Sum(B) or Sum(A+B)
Thomas Karner Sep 1, 2017 5:19 AM (in response to An Pham)Thanks. I know about precalculation 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?

Re: Sum(A) + Sum(B) or Sum(A+B)
Lakshmikandh Karthikeyan Sep 1, 2017 5:24 AM (in response to Thomas Karner )Both has given same results in my app.

Re: Sum(A) + Sum(B) or Sum(A+B)
lakkyreddy devendar Sep 1, 2017 5:29 AM (in response to Thomas Karner )if both fields comes from same table the result should be same for both calculations

Re: Sum(A) + Sum(B) or Sum(A+B)
Kuldeep Tak Sep 1, 2017 5:32 AM (in response to Thomas Karner )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.


Re: Sum(A) + Sum(B) or Sum(A+B)
Thomas Karner Sep 1, 2017 6:38 AM (in response to Tresesco B )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?

Re: Sum(A) + Sum(B) or Sum(A+B)
Tresesco B Sep 1, 2017 6:46 AM (in response to Thomas Karner )Your point b) assumption is correct I believe. Hence, you would get the set analysis data filter benefit even with the rangesum().

Re: Sum(A) + Sum(B) or Sum(A+B)
Rohit Kumar Sep 1, 2017 6:53 AM (in response to Tresesco B )Hi,
sum( {<[Date.Cal.Year]={2017}>} RangeSum([FieldA] , [FieldB]))
This will work if Field A and Field B comes from different table ?

Re: Sum(A) + Sum(B) or Sum(A+B)
Tresesco B Sep 1, 2017 6:56 AM (in response to Rohit Kumar)If the association is fine, it should work.

Re: Sum(A) + Sum(B) or Sum(A+B)
Jonathan Dienst Sep 1, 2017 7:03 AM (in response to Rohit Kumar)The tables are less important in the front end providing they are suitably associated with other.
Sum(RangeSum([FieldA] , [FieldB]) may give odd results if there is not a one to one relationship between A and B within the context of any chart/table dimensions. If for any value of FieldA, there is more than one possible value of FieldB, then the RangeSum() may return only the value of A. In this case, you might be better off with the slightly less efficient Sum(A) + Sum(B) approach.

Re: Sum(A) + Sum(B) or Sum(A+B)
Jonathan Dienst Sep 1, 2017 7:05 AM (in response to Jonathan Dienst )>>if there is not a one to one relationship between A and B within the context of any chart/table dimensions...
Or it may double up the values of A....

Re: Sum(A) + Sum(B) or Sum(A+B)
Jonathan Dienst Sep 1, 2017 7:31 AM (in response to Jonathan Dienst )For example with these two tables
ID A B 1 1 100 1 <null> 100 2 1 50 2 1 200 2 10 <null> 3 2 100 3 3 100 3 4 100 3 4 101 3 4 <null> 3 <null> <null> 4 1 300 4 4 400 ID C 1 100 1 200 2 <null> 3 200 4 300 4 400 Give these results:
Table1: LOAD ID, If(A = '*', Null(), A) as A, If(B = '*', Null(), B) as B Inline [ ID, A, B 1, 1, 100 1, *, 100 2, 1, 200 2, 1, 50 2, 10, * 3, 2, 100 3, 3, 100 3, 4, 100 3, 4, * 3, 4, 101 3, *, * 4, 1, 300 4, 4, 400 ]; Table2: LOAD ID, If(C = '*', Null(), C) as C Inline [ ID, C 1, 100 1, 200 2, * 3, 200 4, 300 4, 400 ];








Re: Sum(A) + Sum(B) or Sum(A+B)
Jonathan Dienst Sep 1, 2017 5:47 AM (in response to Thomas Karner )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.

Re: Sum(A) + Sum(B) or Sum(A+B)
Rohit Kumar Sep 1, 2017 6:02 AM (in response to Jonathan Dienst )Hi Jonathan,
How it will work, when both facts are from different table ?

Re: Sum(A) + Sum(B) or Sum(A+B)
Jaime Aguilar García Sep 1, 2017 12:25 PM (in response to Rohit Kumar)Hi,
just to add to this thread, as a general rule (I'm not sure if it's stated somewhere in help/documentation, etc) you should not use sum(A*B) or sum(A+B) if the fields belong to different tables. I haven't tested it, but in most cases you get ZERO if you try to do it.
But even if you get a result it won't be necessarily right. In other words it would be like an "unsafe" use of the sum in the form sum(A+B)/sum(A*B).
Like it's been said, you should take care of null values because you'll have different results.
Also sum(A) + sum(B) does the sum taking as reference totals, while sum(A+B) will do it record per record (similar to what sumproduct does in Excel). This is the explanation of why it won't work correctly if you try sum(A+B) when fields belong to different tables (also remember, even if you concatenate 2 different tables into one, they are still 2 tables)
so in conclusion you should never take sum(A) + Sum(B) and Sum(A+B) as equivalents,
hope this helps,
regards

Re: Sum(A) + Sum(B) or Sum(A+B)
Thomas Karner Sep 4, 2017 1:46 AM (in response to Jaime Aguilar García )Hi,
thanks for your post. You wrote:
also remember, even if you concatenate 2 different tables into one, they are still 2 tables
I can´t really imagine this. Can you share the explanation why this should happen?

Re: Sum(A) + Sum(B) or Sum(A+B)
Juraj Misina Sep 4, 2017 11:07 AM (in response to Thomas Karner )I gues what Jaime was referring to is a situation when you concatenate two tables into one and different fact fields come from different tables.
Table: LOAD ID, A FROM Source_1; Concatenate(Table) LOAD ID, B FROM Source_2;
Effectively the result is one table, but with NULLs on each row:
ID A B 1 100 NULL 2 200 NULL 3 300 NULL 1 NULL 100 2 NULL 200 3 NULL 300 This really should be treated as if you were working with two separate tables (from aggregation perspective), so use RangeSum() or other approach to account for null values.

Re: Sum(A) + Sum(B) or Sum(A+B)
Rohit Kumar Sep 5, 2017 4:16 AM (in response to Thomas Karner )Hi Thomas,
If I am right, Sum(A+B) =! Sum(A)+Sum(B). IF table is same for A and B and we use range sum so it would be fail for those rows which has null records. Range can give us right result for one table but it could be fail somewhere.
Please correct me if I am wrong!
Best,
Rohit


Re: Sum(A) + Sum(B) or Sum(A+B)
Jonathan Dienst Sep 4, 2017 2:21 AM (in response to Jaime Aguilar García )>>even if you concatenate 2 different tables into one, they are still 2 tables
I don't think that is true. Perhaps you could offer some evidence as to why you believe this.




