topic Re: Bug In Qlik in QlikView

Bug In Qlik

Tue, 19 Jul 2016 15:22:34 GMT

I have a table with the following values

[Total GSV] as WM_TotalGSV ,

[Retro Discount] as WM_RetrospectiveDiscount ,

[Standard Discount] as WM_StandardDiscount ,

Which I store into a temp table.

I then read the data into the required data and perform the following calculation

WM_TotalGSV - WM_RetrospectiveDiscount - WM_StandardDiscount as WM_NetSales,

For some reason the figure

sum(WM_NetSales)

Is different from

sum(WM_TotalGSV) - sum(WM_RetrospectiveDiscount ) - sum(WM_StandardDiscount)

This is when I create a chart selecting the whole data set. If select on a line by line, or group of lines the figures match. The difference between the values is sum(WM_RetrospectiveDiscount ) (WM_StandardDiscount is zero for all lines).

I can't see any reason why the figures we different.

Thanks

Andy

Re: Bug In Qlik

jonathandienst — Tue, 19 Jul 2016 15:30:49 GMT

Not a bug at all! The expression

WM_TotalGSV - WM_RetrospectiveDiscount - WM_StandardDiscount

will evaluate to null if ANY of the three field values is null. So the values of the other fields for those reords will be lost when you Sum those values. The alternative of summing them individually does not do this.

This is consistent with how SQL databases handle null values.

Re: Bug In Qlik

effinty2112 — Tue, 19 Jul 2016 15:38:54 GMT

Hi Andy,

Instead of :

WM_TotalGSV - WM_RetrospectiveDiscount - WM_StandardDiscount

Try

RangeSum(WM_TotalGSV, - WM_RetrospectiveDiscount, - WM_StandardDiscount)

It could be that null values are messing up this horizontal calculation. RangeSum handles nulls as if they are zero value.

Cheers

Andrew

Re: Bug In Qlik

Wed, 20 Jul 2016 06:56:59 GMT

Thank you both for your help.

Changing the calculation to use RangeSum solved the problem.