topic Re: Partial sum of rounding in QlikView

Partial sum of rounding

sibrulotte — Wed, 12 Mar 2014 20:38:08 GMT

Hi,

have any of you come across this:

(sum({$<[%CLE_Type_Transaction_ID] -= {'EB', 'EA'}, [Numéro du type d'écriture] -= {'FERM'} >}[Montant cumulé aad]* %CR003M))

This returns a two decimal number.

When I wrap it with round, I get a rounded number.

Unfortunatly, the partial sum didn't get the memo...

It still treats the numbers as two decimals, and returns a rounded number that is off.

See my jpgs for as an exemple.

Re: Partial sum of rounding

Wed, 12 Mar 2014 20:42:51 GMT

I dont quite understand the issue. The subtotal is rounded off from 360381431,61 to 360 381 432

since the decimal place is 61 (>50) its rounded.

if num is 1.5 will be rounded to 2

if 1.8 will be 2

if 1.4 will be 1.

Can you explain a little more or am I missing something?

Note: if you just wanna drop the decimal part without rounding you can try floor().

Re: Partial sum of rounding

sibrulotte — Thu, 13 Mar 2014 12:50:44 GMT

Look at the rounded numbers:

58 668 858 + 302 752 512 - 1 039 939 = 360 381 431 NOT 360 381 432

Sorry for the long digits, I just gave you the precises numbers.

So the partial sum is still using the unrounded numbers. It's driving me crazy.

Re: Partial sum of rounding

ashwanin — Thu, 13 Mar 2014 13:15:55 GMT

Which qv version you are using.

because what i know is that the qv automatically do the same thing as Ajay wrote ie. it auto convert the last digits to the above value .

This happens when you have the long decimal digits.

Re: Partial sum of rounding

ashwanin — Thu, 13 Mar 2014 13:16:43 GMT

and sometimes lowers the value ,as per the last digit (ie below 4)

Re: Partial sum of rounding

Thu, 13 Mar 2014 15:20:22 GMT

I see what you are getting to but qlikview doesn't add the rounded off numbers. It adds the actual numbers and rounds off the result.

But as you mentioned it is misleading. This is interesting..

Re: Partial sum of rounding

sibrulotte — Thu, 13 Mar 2014 15:59:28 GMT

So rounding tlike this worked:

(sum({$<[%CLE_Type_Transaction_ID] -= {'EB', 'EA'}, [Numéro du type d'écriture] -= {'FERM'} >}round([Montant cumulé aad])* %CR003M))

Re: Partial sum of rounding

sibrulotte — Tue, 18 Mar 2014 17:47:38 GMT

SO I am taking out the correct answer that I had put to my own self...

hehe.

This is horrendous and skews the results since obviously every single line behind the code is rounded, creating havoc in my results.

So I am back to complaining that the partial sum doesn't get that the results are rounded, and takes the unrounded values to show a rounded sum.

I'm using QV 11 SR2

Re: Re: Partial sum of rounding

jerem1234 — Tue, 18 Mar 2014 18:18:23 GMT

You'll have to create two sums, one for regular values and another for your partial sums. I've attached an example qvw. I used a formula like:

if(dimensionality() =2, round(sum(Value)), sum(aggr(round(sum(Value)), Type, Product)))

You should be able to accommodate this formula to your fields and calculations. The important parts are dimensionality() to distinguish between the regular values and your partial sums (the 2 may change depending on how much aggregation you are doing) and sum(aggr()) to sum the required rounded values (the fields listed will be dependent on your data).

EDIT: You may actually be able to get away with just:

sum(aggr(round(sum(Value)), Type, Product))

Hope this helps!

Re: Partial sum of rounding

sibrulotte — Tue, 18 Mar 2014 18:27:14 GMT

Well that definitly seems to work in your file.

You have no idea how much coding time this will require though for me. :*(

Re: Partial sum of rounding

jerem1234 — Tue, 18 Mar 2014 18:28:56 GMT

I don't know if you saw my edit, but you may be able to get away with just:

sum(aggr(round(sum(Value)), Type, Product))

This should reduce the formula a bit.

Re: Partial sum of rounding

sibrulotte — Tue, 18 Mar 2014 18:32:24 GMT

Yeah, did, I tested in your file and it worked in deed.

But I have at least 40 some expressions to change with each 3 to 4 dimensions, and that is without taking into account a sandbox that has 20 some dimensions and 20 some expressions. Using AGGR requires that I explicitly state which dimension I am agrregating for right?

Re: Partial sum of rounding

jerem1234 — Tue, 18 Mar 2014 18:42:30 GMT

Unfortunately yes, but if some charts use the same dimensions, you could create a variable vAggr with a value:

Type, Product

Then for your expression your could do:

sum(aggr(round(sum(Value)), $(vAggr)))

Other than that, you could leave it the way it is and just let the user know that the rounding takes place after the summing (which therefore the partial sum is not always the sum of the rows above). In all honesty, this probably gives more of an accurate sum.

Hope this helps!