topic Re: Calculation error in QlikView

Calculation error

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

I have a table (below) that calculates the difference between the selected year and the previous year but the calculations are not accurate and are out by 0.01 in the highlighted rows below.

Could this be due to an error in my expression (Change)-

=
(
SUM({$<DYW_flag={'Y'}, Year=, AcademicYearShort ={$(=max(AcademicYearShort))}>}[credits])/
SUM({$<Year=, AcademicYearShort ={$(=max(AcademicYearShort))}>}[credits])
)
-
(
SUM({$<DYW_flag={'Y'}, Year=, AcademicYearShort ={$(=max(AcademicYearShort)-1)}>}[credits])/
SUM({$<Year=, AcademicYearShort ={$(=max(AcademicYearShort)-1)}>}[credits])
)

Note: I had to create new field called AcademicYearShort because my Year field was presented as an academic year i.e. 2015-16

Thanks

Greg

Re: Calculation error

prma7799 — Mon, 26 Jun 2017 09:45:38 GMT

Could you please share some sample data..

Or try to convert all values in number format.

Re: Calculation error

MK_QSL — Mon, 26 Jun 2017 09:46:04 GMT

Difficult to say anything without looking into the original app or data.. but you can check below blog post..

Rounding Errors

Re: Calculation error

niclaz79 — Mon, 26 Jun 2017 09:53:27 GMT

Hi, most likely this is due to that the numbers presented are rounded (data has more than 2 decimal points).

So for instance this year is rounded from 0.467% to 0.47% , last year is rounded from 0.444 to 0.44%. This would give a difference of 0.47%-0.44% = 0.03%

However the real calculation would be 0.467%-0.444% = 0.023% which then is rounded to 0.02%

So the difference is not in the calculations, it's in the way the data is presented.

In my opinion this is not an error, it's just a lack of granularity and can be easily explained.

Hope this helps!

Re: Calculation error

Anonymous — Mon, 26 Jun 2017 10:12:14 GMT

Thanks Niclas

Do you know if it is possible in Qlikview to perform the calculation on the rounded data rather than the raw data.

Re: Calculation error

prma7799 — Mon, 26 Jun 2017 10:25:27 GMT

Yeah It is possible.

Re: Calculation error

niclaz79 — Mon, 26 Jun 2017 11:01:27 GMT

Greg, if you use Round() in your calculations (all three) the result should be the same. However in my opinion that is less correct than what you have now since you are removing data accuracy. That's up to you though

Also, if you agree with my response above please mark it as correct so others with the same question can find it.