topic Re: Re: QlikView formula error in QlikView

QlikView formula error

Fri, 21 Nov 2014 04:10:35 GMT

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...

Re: QlikView formula error

JonnyPoole — Fri, 21 Nov 2014 04:35:29 GMT

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)

Re: QlikView formula error

tresB — Fri, 21 Nov 2014 04:38:56 GMT

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.

Re: QlikView formula error

sujeetsingh — Fri, 21 Nov 2014 04:48:40 GMT

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.

Re: QlikView formula error

jonathandienst — Fri, 21 Nov 2014 05:03:21 GMT

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

Re: QlikView formula error

Fri, 21 Nov 2014 06:30:32 GMT

Kindly find the attachment above...

can any one let me know how to I avoid null figures?

Re: QlikView formula error

jagan — Fri, 21 Nov 2014 06:44:25 GMT

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.

Re: Re: QlikView formula error

tresB — Fri, 21 Nov 2014 06:44:50 GMT

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)

Re: QlikView formula error

Fri, 21 Nov 2014 09:15:38 GMT

those three columns are in three different tables but I have join those tables in qlikview

note all three columns contain null values

Re: QlikView formula error

Fri, 21 Nov 2014 09:20:44 GMT

Okay that's makes seance.. what I have to do if there are null values?

Re: Re: QlikView formula error

tresB — Fri, 21 Nov 2014 10:14:36 GMT

To handle nulls, use rangesum(), like:

Rangesum(F2,-F3,-F4)

Or, for multiple values

Sum(Rangesum(F2,-F3,-F4))

PFA

Re: QlikView formula error

Mon, 24 Nov 2014 07:56:53 GMT

=rangesum(ALLOCATION) - (ACTUAL_SAH + PROJECTION)

=rangesum(ALLOCATION) - rangesum (ACTUAL_SAH) - rangesum(PROJECTION)

This is right?

Re: QlikView formula error

tresB — Mon, 24 Nov 2014 08:04:41 GMT

Nope! Try like:

=rangesum(ALLOCATION, -ACTUAL_SAH,-PROJECTION)

Re: QlikView formula error

Mon, 24 Nov 2014 08:15:38 GMT

no I get 0

but below sum gives me -2080198

=sum(ALLOCATION) - sum (ACTUAL_SAH) - sum(PROJECTION)

Re: QlikView formula error

jonathandienst — Mon, 24 Nov 2014 08:18:36 GMT

Sum(rangesum(ALLOCATION, -ACTUAL_SAH,-PROJECTION))

Re: QlikView formula error

tresB — Mon, 24 Nov 2014 08:47:35 GMT

As I mentioned earlier(and been suggested by Jonathan) for multiple occurances, use sum() alongwith that. If even that doesn't help, you could share a sample qvw that demonstrates the issue.

Re: QlikView formula error

Mon, 24 Nov 2014 10:49:00 GMT

Please add me so that can send you the informations

Re: QlikView formula error

tresB — Mon, 24 Nov 2014 10:55:27 GMT

Why don't you attach here? if you are worried about data security, have a look here:Preparing examples for Upload - Reduction and D... | Qlik Community

Re: QlikView formula error

Tue, 25 Nov 2014 04:57:44 GMT

Hi, Kindly find the attachments. please help me

Re: QlikView formula error

jagan — Tue, 25 Nov 2014 06:17:50 GMT

Hi,

Can you attach the actual file? or the script of the table? Assume that after join you have below table try like this

LOAD

Alt(ALLOCATION, 0) AS ALLOCATION,

Alt(ACTUAL_SAH, 0) AS ACTUAL_SAH,

Alt (PROJECTION, 0) AS PROJECTION

FROM DataSource;

In the above script we handled the null values and convert it to zero.

Now you can straight away use

=sum(ALLOCATION) - sum (ACTUAL_SAH) - sum(PROJECTION)

sum(ALLOCATION - ACTUAL_SAH - PROJECTION)

both will return same values.

Hope this helps you.

Regards,

Jagan.