Bug In Qlik

[#1]

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

[jonathandienst]

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.

[effinty2112 · Accepted solution]

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

[#4]

Thank you both for your help.

Changing the calculation to use RangeSum solved the problem.