QlikView formula error

[#1]

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

[tresesco]

To handle nulls, use rangesum(), like:

Rangesum(F2,-F3,-F4)

Or, for multiple values

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

PFA

[#3]

=rangesum(ALLOCATION) - (ACTUAL_SAH + PROJECTION)

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

This is right?

[tresesco]

Nope! Try like:

=rangesum(ALLOCATION, -ACTUAL_SAH,-PROJECTION)

[#5]

no I get 0

but below sum gives me -2080198

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

[jonathandienst]

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

[tresesco]

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.

[#8]

Please add me so that can send you the informations

[tresesco]

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

[#10]

Hi, Kindly find the attachments. please help me

• OC_ACTUAL.xlsx13.9 K Preview
• OC_Product_CAT_Wise.xlsx55.2 K Preview
• OC_ProductWise.xlsx13.7 K Preview
• test.qvw

[jagan]

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)

OR

sum(ALLOCATION - ACTUAL_SAH  - PROJECTION)

both will return same values.

Hope this helps you.

Regards,

Jagan.