Rounding issue - please help

Wed, 15 Aug 2012 11:27:23 GMT

Hello,

I have a chart which has 3 columns:

Column A which sums hours for a specific week,

Column B which sums hours for previous week

Column C which shows the difference between the 2

The problem is that is Column A is showing 20 from true figure of 20.25 , Column B is showing 27 from true figure of 26.5 yet Column C is showing (6)

Basically I want consistent rounding throughout so would want Column C to show (7)

The data is fed into the script in a inline table

Load * Inline

[Select11, Equ11,

Agency hours,"(SUM({}IF(Employstatus='999001302',(basichours+nextdayhours+othours+nextdayot+salaryhours)))+SUM({}Agy)+Sum({}AAAGY))"

and the data is shown in a table chart as follows

Expression for column A with Integer number format settings #,##0

=$(=replace(Equ11,'{}','{<Nice_Week=,Week_ClassNum={$(=min(Week_ClassNum))}>}'))

Expression for column B with Integer number format settings #,##0;(#,##0) i.e. to show any negative numbers as (...)

=$(=replace(Equ11,'{}','{<Nice_Week=,Week_ClassNum={$(=min(Week_ClassNum)+1)}>}'))

Expression for column C with Integer number format settings #,##0

=$(=replace(Equ11,'{}','{<Nice_Week=,Week_ClassNum={$(=min(Week_ClassNum))}>}'))-$(=replace(Equ11,'{}','{<Nice_Week=,Week_ClassNum={$(=min(Week_ClassNum)+1)}>}'))

I assume I need to use the FLOOR function to avoid rounding up and down variably (which presumably is causing the issue) but where do I need to add the floor command in the above expression? I have tried experimenting with the Number Format sections to no avail.

Any help would be much appreciated.

Thanks.

Re: Rounding issue - please help

swuehl — Wed, 15 Aug 2012 11:58:30 GMT

Have you tried using round() function, like this:

round(

$(=replace(Equ11,'{}','{<Nice_Week=,Week_ClassNum={$(=min(Week_ClassNum))}>}'))

)

round(
$(=replace(Equ11,'{}','{<Nice_Week=,Week_ClassNum={$(=min(Week_ClassNum)+1)}>}'))

)

Re: Rounding issue - please help

Wed, 15 Aug 2012 13:12:54 GMT

Thanks Swuehl

topic Rounding issue - please help in QlikView

Rounding issue - please help

Re: Rounding issue - please help

Re: Rounding issue - please help