Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Not applicable

Qlikview rounding errors (was: some kind of 'group by' on calculated dimension?)

I'm new to Qlikview and I'm trying to generate a variance chart on the difference of objects weights between the first time the object is weighted and the second time.

I have a table containing the fields object-ID, weight-index and weight. There are objects with only one weighting (weight-index = 1), objects with both a weight-index 1 and 2, and a few objects with only a second weighting (weight-index = 2)

Now I want a chart that shows me, for all objects with 2 weightings, how many objects there are per difference between first and second weighting.

for example:

object_idweight_indexweight
113.09
213.05
223.03
323.01
413.02
423.07
513.03
523.05
613.05

I managed to have a pivot-chart showing

object_idweight 1weight 2difference
23.053.03-0.02
43.023.070.05
53.033.050.02

using object_id as dimension and

weight 1 = sum({<weight_index = {1}, object_id = P({$<weight_index = {2}>}) - E({$<weight_index = {1}>})>} weight)

weight 2 = sum({<weight_index = {2}, object_id = P({$<weight_index = {2}>}) - E({$<weight_index = {1}>})>} weight)

difference = [weight 2] - [weight 1]

as expressions

But now I want a chart which shows

differenceamount of objects
-0.021
0.051
0.021

(ofcourse in this example is the amount always 1, but that won't be the case with my real data)

How can I do this best?

I have been experimenting with calculated dimensions as such:

difference = aggr({$<object_id = P({$<weight_index = {2}>}) - E({$<weight_index = {1}>})>} sum({<weight_index = {2}>} weight) - sum({<weight_index = {1}>} weight), object_id)

andas expression:

amount of objects = count(object_id)

But that gives strange results..

1 Solution

Accepted Solutions
Not applicable

some kind of 'group by' on calculated dimension?

I found the solution: I have to use the round function before I aggregate..

This makes my calculated dimension like this:

=aggr(round(sum({$<object_id = P({$<weight_index = {2}>}) - E({$<weight_index = {1}>}), weight_index= {2}>} weight) - sum({<object_id = P({$<weight_index= {2}>}) - E({$<weight_index= {1}>}), weight_index= {1}>} weight), 0.01), object_id)


I still think this is a problem of qlikview that it internally works with floating point numbers (which don't always have an exact representation of a number, but use a value as close as possible) and nowhere to define what kind of format the numbers of the source file are, or built-in corrections (like automatically applying the round function) in calculations to prevent this kind of unexpected behaviour

6 Replies
MVP
MVP

some kind of 'group by' on calculated dimension?

A script solution:

,if(object_id=previous(object_id),weight-previous(weight)) as difference

dimension = difference (suppress when null)
expression = count(distinct object_id)

A chart solution (looks like you were close):

dimension = aggr(if(count(weight_index)=2,sum({<weight_index={2}>} weight)-sum({<weight_index={1}>} weight)),object_id)
expression = count(distinct object_id)

See attached.

Not applicable

some kind of 'group by' on calculated dimension?

Thanks for your reply. However, I get exactly the same results with your calculated dimension as with mine (except that with your dimension, there is the danger that an object_id with two times a weight_index = 1 is also considered (this happens occasionaly when an object is accidently weighted two times), therefore my explicit check on weight_index = 1 and weight_index = 2)

The result that I currently have (both with your and my expression) is like this (there are 2 types of objects: closed or open which I didn't mention before, however the id is unique, so that shouldn't matter):

Variance
Difference # open objects # closed objects
104197
-0,0501
-0,0435
-0,0318
-0,0323
-0,021636
-0,011920
-0,00999999999999981633
02145
0,0099999999999998217
0,01812
0,0210
0,02310
0,0340
0,0322
0,0420
0,0421
0,0510
0,0602
0,0701
0,0901
0,1110

Like you can see: I get 2 times -0.03, 2x 0.02, 2x 0.03, 2x 0.04 and even a -0.0099999999999998 and 0.0099999999999998 while the maximum accuracy of the source (csv-)file is 0.010 (formatted like 0.000 but accurate to 0.01)

In another chart which shows the difference between the 2 weightings I also see these rounding errors:

Bakgewicht per weging
Object ID object_type weight 1 weight 2 difference
...............
105156Gesloten3,223,21-0,01
153581Gesloten3,163,15-0,01
162698Gesloten3,163,15-0,01
105664Gesloten3,223,21-0,01
159974Open3,043,03-0,01
159970Open3,023,01-0,01
122671Gesloten3,073,06-0,0099999999999998
122414Gesloten3,053,04-0,0099999999999998
123925Gesloten3,033,02-0,0099999999999998
120697Gesloten3,053,04-0,0099999999999998
126694Gesloten3,093,08-0,0099999999999998
126278Gesloten3,073,06-0,0099999999999998
124399Gesloten3,073,06-0,0099999999999998
112944Open3,053,04-0,0099999999999998
127231Gesloten3,073,06-0,0099999999999998
131458Gesloten3,093,08-0,0099999999999998
113134Open3,033,02-0,0099999999999998
107337Open3,013-0,0099999999999998
115471Open3,033,02-0,0099999999999998
114649Open3,013-0,0099999999999998
114399Open3,033,02-0,0099999999999998
115148Open3,053,04-0,0099999999999998
116171Open3,033,02-0,0099999999999998
116086Open3,053,04-0,0099999999999998
102434Open3,053,04-0,0099999999999998
102657Open3,053,04-0,0099999999999998
102728Open3,053,04-0,0099999999999998
103221Open3,053,04-0,0099999999999998
155772Gesloten3,153,14-0,0099999999999998
154394Gesloten3,113,1-0,0099999999999998
153644Gesloten3,133,12-0,0099999999999998
152958Gesloten3,133,12-0,0099999999999998
150063Gesloten3,133,12-0,0099999999999998
155841Gesloten3,113,1-0,0099999999999998
150173Gesloten3,133,12-0,0099999999999998
155381Gesloten3,133,12-0,0099999999999998
150913Gesloten3,153,14-0,0099999999999998
103526Open3,053,04-0,0099999999999998
103998Open3,033,02-0,0099999999999998
159150Open3,053,04-0,0099999999999998
161172Gesloten3,173,16-0,0099999999999998
158605Gesloten3,193,18-0,0099999999999998
110337Gesloten3,153,14-0,0099999999999998
109680Gesloten3,153,14-0,0099999999999998
111909Gesloten3,193,18-0,0099999999999998
108670Gesloten3,213,2-0,0099999999999998
158077Gesloten3,213,2-0,0099999999999998
158703Gesloten3,213,2-0,0099999999999998
110176Gesloten3,173,16-0,0099999999999998
158713Gesloten3,213,2-0,0099999999999998
108167Gesloten3,213,2-0,0099999999999998
105928Gesloten3,213,2-0,0099999999999998
105305Gesloten3,213,2-0,0099999999999998
105583Gesloten3,213,2-0,0099999999999998
158827Gesloten3,213,2-0,0099999999999998
100457Open3,023,020
122297Gesloten3,083,080
120131Gesloten3,053,050
120216Gesloten3,043,040
121615Gesloten3,073,070
121685Gesloten3,043,040
121694Gesloten3,043,040
126926Gesloten3,063,060
125325Gesloten3,033,030
125090Gesloten3,073,070
124568Gesloten3,063,060
120335Gesloten3,063,060
125334Gesloten3,043,040
132145Gesloten3,073,070
133792Gesloten3,063,060
130493Gesloten3,073,070
109832Gesloten3,153,150
161699Gesloten3,183,180
159978Open3,033,030
123847Gesloten3,023,030,0099999999999998
122115Gesloten33,010,0099999999999998
124666Gesloten3,063,070,0099999999999998
126493Gesloten3,063,070,0099999999999998
126727Gesloten3,063,070,0099999999999998
122795Gesloten3,063,070,0099999999999998
123473Gesloten3,043,050,0099999999999998
130472Gesloten3,063,070,0099999999999998
131658Gesloten3,063,070,0099999999999998
130629Gesloten3,063,070,0099999999999998
127847Gesloten3,063,070,0099999999999998
128986Gesloten3,083,090,0099999999999998
128769Gesloten3,063,070,0099999999999998
113851Open33,010,0099999999999998
114665Open3,043,050,0099999999999998
122695Gesloten3,083,090,0099999999999998
153752Gesloten3,123,130,0099999999999998
151202Gesloten3,123,130,0099999999999998
108742Gesloten3,23,210,0099999999999998
130612Gesloten3,073,080,01
133624Gesloten3,053,060,01
127010Gesloten3,053,060,01
132636Gesloten3,053,060,01
132761Gesloten3,053,060,01
131156Gesloten3,073,080,01
135058Gesloten3,073,080,01
129510Gesloten3,073,080,01
135048Gesloten3,053,060,01
...............

The dimensions here are object_id and object_type
The expressions:
weight 1 = sum({<weight_index= {1}, object_id = P({$<weight_index = {2}>}) - E({$<weight_index = {1}>})>} weight)
weight 2 = sum({<weight_index= {2}, object_id = P({$<weight_index = {2}>}) - E({$<weight_index = {1}>})>} weight)
difference = [weight 2] - [weight 1]

The number format is set to "expression default"
and the accuracy of the input-file is 0.010

So I assume the weird result I get is because of some rounding errors which aren't even displayed (like those 2x -0.03) .. But I don't understand where and why qlikview is making those errors using normal additions and substraction.

Here you can see a sniplet of the import file:
122297|12.01.2010|1|3,080|
114665|12.01.2010|0|3,050|
155225|12.01.2010|1|3,150|
159155|12.01.2010|0|3,040|
122795|12.01.2010|1|3,070|
135402|12.01.2010|1|3,050|
122414|12.01.2010|1|3,040|
155432|12.01.2010|1|3,110|
102553|12.01.2010|1|3,170|
124873|12.01.2010|1|3,030|
120697|12.01.2010|1|3,040|
131458|12.01.2010|1|3,080|
125334|12.01.2010|1|3,040|
105664|12.01.2010|1|3,210|
154990|12.01.2010|1|3,140|
122695|12.01.2010|1|3,090|
121685|12.01.2010|1|3,040|
121615|12.01.2010|1|3,070|

And this is the script I use to import the file:

object_data:
LOAD @1 as object_id, // Bak ID
MakeDate(Mid(@2, 7, 4), Mid(@2, 4, 2), Mid(@2, 1, 2)) as object_scandate
@3 as type_id,
@4 as weight,
1 as weight_index
FROM
D:\object_data_first_weight.txt
(txt, codepage is 1252, no labels, delimiter is '|', no quotes);

LOAD @1 as object_id, // Bak ID
MakeDate(Mid(@2, 7, 4), Mid(@2, 4, 2), Mid(@2, 1, 2)) as object_scandate
@3 as type_id,
@4 as weight,
2 as weight_index
FROM
D:\object_data_second_weight.txt
(txt, codepage is 1252, no labels, delimiter is '|', no quotes);

syed_muzammil
Contributor II

some kind of 'group by' on calculated dimension?

Hi,

Can't you use the Number tab in the chart properties and round off your expression to the number of decimals you want? You will be getting the same result this way too.

Regards,

Syed.

syed_muzammil
Contributor II

some kind of 'group by' on calculated dimension?

hi,

I mean set the number format to Fixed 3 decimals.

Not applicable

some kind of 'group by' on calculated dimension?

I can set the number format to fixed, which I did before.. and that way the chart containing both the weights per object_id indeed looks better.. In the calculated dimension I can't use the number formatting, but there I can use the the num() function and visually it indeed formats the numbers.. but the numbers are still listed multiple times, as they are actually (internaly) not the same, but only displayed the same:

Variantie
Difference# open objects
# closed objects
104197
-0,05001
-0,04035
-0,03018
-0,03023
-0,0201636
-0,0101920
-0,0101633
0,0002145
0,010217
0,010812
0,02010
0,020310
0,03040
0,03022
0,04020
0,04021
0,05010
0,06002
0,07001
0,09001
0,11010

I still don't get why Qlikview gives me this: 3.07 - 3.06 = -0.0099999999999998
while it should give -0.01.. There is no way that there can be a rounding error in such a simple substraction? is there?
In the source data-file those numbers aree 3,070 and 3,060.. and 3.070 - 3.060 should still give me -0.010

And I'm sure this is the cause of my problem: the duplicate 'difference' value's in my chart above..; Qlikview makes some rounding errors, but doesn't display them as they are too small (or in the current case formatted fixed to 3), hence the actual internal values are not the same for qlikview thus are displayed as individual numbers visually giving duplicate values.

Not applicable

some kind of 'group by' on calculated dimension?

I found the solution: I have to use the round function before I aggregate..

This makes my calculated dimension like this:

=aggr(round(sum({$<object_id = P({$<weight_index = {2}>}) - E({$<weight_index = {1}>}), weight_index= {2}>} weight) - sum({<object_id = P({$<weight_index= {2}>}) - E({$<weight_index= {1}>}), weight_index= {1}>} weight), 0.01), object_id)


I still think this is a problem of qlikview that it internally works with floating point numbers (which don't always have an exact representation of a number, but use a value as close as possible) and nowhere to define what kind of format the numbers of the source file are, or built-in corrections (like automatically applying the round function) in calculations to prevent this kind of unexpected behaviour