6 Replies Latest reply: Oct 5, 2010 5:48 AM by RobinABM RSS

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

    RobinABM

      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..

        • some kind of 'group by' on calculated dimension?
          John Witherspoon

          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.

            • some kind of 'group by' on calculated dimension?
              RobinABM

              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);

                • some kind of 'group by' on calculated dimension?
                  Muzammil Syed

                  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.

                    • some kind of 'group by' on calculated dimension?
                      Muzammil Syed

                      hi,

                      I mean set the number format to Fixed 3 decimals.

                        • some kind of 'group by' on calculated dimension?
                          RobinABM

                          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.

                            • some kind of 'group by' on calculated dimension?
                              RobinABM

                              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