Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Guys,
Am facing formula error in QlikView if you see the image below I would give an idea
basically when its come to maths sum(A) - sum(B+C) and sum(A) - sum(B) - sum(C) are output same value but
in QlikView I get two deference figures. can any one explain me why is this? for your reference I have attached an excel file
Thanks in advance!
Message was edited by: Mafaz Jaufer Please find the QVD attachment...
sum(A+B) may result in joining records across tables that can result in indivudal values of A in a table being linked with multiple values of B in another table and magnifying the sum, whereas sum(A) + sum(B) will just add up the rows in each table and sum the totals.
There can be other things like NULLs which would zero out some of the joined records in a sum(A+B) situation where A or B were null .
More understanding of where these 3 fields sit in your data model would help
i suggest creating a table box with the 3 values to show the combinations of records involved and compare with a straight table with all 3 as sum(expressions)
PFA, it's working fine with your sample data. Issue might be specific to your application(possibly nulls could be the culprit). Try to share your sample qvw that demonstrates the issue.
Poole, is saying the right fact well if all is fine then the expressions should give same output.
Try making a sample and provide us.
Hi
The difference is most likely caused by nulls in fields B or C, because
B + null() = null() and evaluates to 0 in a sum, regardless of the value of B (so this B is not in the total)
null() + C = null() and evaluates to 0 in a sum, regardless of the value of C (so this C is not in the total)
The correct way do this is to expand the sums, or to use Rangesum to add the values. ie
Sum(RangeSum(A, B))
HTH
Jonathan
Kindly find the attachment above...
can any one let me know how to I avoid null figures?
Hi,
Is both the columns are in the same table? Is any null values are there in this columns? If both the columns are in different tables then you may get different because of joins etc.
Regards,
Jagan.
Check attached qvw. You made a mistake taking a non-numeric field F1 (in 2nd & 3rd exp) in sum.
Update: And yes, field names are case sensitive (there were errors as well)
those three columns are in three different tables but I have join those tables in qlikview
note all three columns contain null values
Okay that's makes seance.. what I have to do if there are null values?