Calculation error

[Anonymous]

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

[prma7799]

Could you please share some sample data..

Or try to convert all values in number format.

[MK_QSL]

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

Rounding Errors

[niclaz79 · Accepted solution]

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!

[Anonymous]

Thanks Niclas

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

[prma7799]

Yeah It is possible. 

[niclaz79]

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.