topic Re: Rounding Issue in QlikView

Rounding Issue

cbaqir — Wed, 03 Feb 2016 19:43:04 GMT

I am aware of Rounding Errors but can't figure out how to fix it.

Column 1

Sum({$<FISCAL_YEAR=,APP_DATE={">$(=Max({<ACT_AMT={'>0'}>}APP_DATE))"}, FCST_DATE = {"$(=date(max(FCST_DATE), 'MM-DD-YYYY'))"}>}FCST_AMT)

Column 2 uses a variable called $(vLTETC) and defined as: round(Sum({$<FISCAL_YEAR=,APP_DATE={">$(=Max({<ACT_AMT={'>0'}>}APP_DATE))"}, FCST_DATE = {"$(=date(max(FCST_DATE), 'MM-DD-YYYY'))"}>}FCST_AMT),1000 ) /1000

These numbers have tied out in the past using the round(xxx,1000 ) /1000. However, you can see that 847,326,771 should round to 847,327 NOT 847,324.

For the record, I have a text box that CORRECTLY displays $(vLTETC) as 847,327 which confuses me even more.

Any ideas?

Re: Rounding Issue

sunny_talwar — Wed, 03 Feb 2016 19:47:16 GMT

What do you see for this (without the division by 1000):

round(Sum({$<FISCAL_YEAR=,APP_DATE={">$(=Max({<ACT_AMT={'>0'}>}APP_DATE))"}, FCST_DATE = {"$(=date(max(FCST_DATE), 'MM-DD-YYYY'))"}>}FCST_AMT),1000)

Re: Rounding Issue

swuehl — Wed, 03 Feb 2016 19:47:41 GMT

As far as I see, you sum a set of rounded values which is not the same as round a set of the original values.

If you want max. accurate numbers, your rounding should happen as last step.

Re: Rounding Issue

cbaqir — Wed, 03 Feb 2016 19:50:36 GMT

Re: Rounding Issue

cbaqir — Wed, 03 Feb 2016 19:51:54 GMT

How would I do that?

And why would the variable in the text box round correctly but not in the chart?

Re: Rounding Issue

sunny_talwar — Wed, 03 Feb 2016 19:54:29 GMT

May try this:

Round(Sum(Aggr(Sum({$<FISCAL_YEAR=,APP_DATE={">$(=Max({<ACT_AMT={'>0'}>}APP_DATE))"}, FCST_DATE = {"$(=date(max(FCST_DATE), 'MM-DD-YYYY'))"}>}FCST_AMT), YourChartDimensions)),1000)

Re: Rounding Issue

cbaqir — Wed, 03 Feb 2016 19:58:18 GMT

Re: Rounding Issue

cbaqir — Wed, 03 Feb 2016 20:01:01 GMT

I switched from using the round(xxx,1000 ) /1000 to using round(xxx) /1000 and in Number using Fixed to 0 decimals. It looks right but I am concerned that it may cause other issues which is why we started using round(xxx,1000 ) /1000.

Thanks for the quick responses, guys.

Re: Rounding Issue

sunny_talwar — Wed, 03 Feb 2016 20:01:21 GMT

I am out of options. I am sure Stefan will find an answer for you

Re: Rounding Issue

swuehl — Wed, 03 Feb 2016 20:01:35 GMT

What's your total mode for this expression? Expression total or sum-of-rows?

Re: Rounding Issue

cbaqir — Wed, 03 Feb 2016 20:03:18 GMT

sum of rows

Re: Rounding Issue

swuehl — Wed, 03 Feb 2016 20:06:08 GMT

What about using total mode = expression total?

Re: Rounding Issue

cbaqir — Wed, 03 Feb 2016 20:06:36 GMT

You are a genius!

Re: Rounding Issue

sunny_talwar — Wed, 03 Feb 2016 20:12:52 GMT

I second that