topic Re: Round numbers (%) in expression in QlikView

Round numbers (%) in expression

Wed, 28 Dec 2016 10:54:15 GMT

Hi,

I try get MAX values in bar chart for each category (picture below). I got it, but I have a problem with round numbers. May I ask for help?

This is code, which I need for MAX values in Category in %. It works fine.

=if(RANK(AGGR(Sum({<DATUM_ID = {">=$(=datum_od) <=$(=datum_do)"}>} PRIJATE)/ Sum(TOTAL <AREA> {<DATUM_ID = {">=$(=datum_od) <=$(=datum_do)"}>} PRIJATE) ,AREA, TYPE)) = 1,rgb(80,80,80))

p.s. "DATUM_ID" means DATE

Re: Round numbers (%) in expression

sunny_talwar — Wed, 28 Dec 2016 12:32:49 GMT

You are looking to show 94,339622641509% as 94%? May be this:

Num(Sum({<DATUM_ID = {">=$(=datum_od) <=$(=datum_do)"}>} PRIJATE)/ Sum(TOTAL <AREA> {<DATUM_ID = {">=$(=datum_od) <=$(=datum_do)"}>} PRIJATE), '##%')

Or on the Number's tab of the chart properties you can select Integer and then check 'Show in Percent (%)'

Re: Round numbers (%) in expression

Wed, 28 Dec 2016 12:44:50 GMT

I edited my code. It is right:

=if(RANK(AGGR(Sum({<DATUM_ID = {">=$(=datum_od) <=$(=datum_do)"}>} PRIJATE)/ Sum(TOTAL <AREA> {<DATUM_ID = {">=$(=datum_od) <=$(=datum_do)"}>} PRIJATE) ,AREA, TYPE)) = 1,rgb(80,80,80))

but Num(..., '##%') doesn't work

Re: Round numbers (%) in expression

sunny_talwar — Wed, 28 Dec 2016 12:47:11 GMT

This code is used for background color, right?

=if(RANK(AGGR(Sum({<DATUM_ID = {">=$(=datum_od) <=$(=datum_do)"}>} PRIJATE)/ Sum(TOTAL <AREA> {<DATUM_ID = {">=$(=datum_od) <=$(=datum_do)"}>} PRIJATE) ,AREA, TYPE)) = 1,rgb(80,80,80))

You don't need formatting for color. You need this in your original expression.....

Re: Round numbers (%) in expression

Wed, 28 Dec 2016 12:51:06 GMT

No, it is code for Show Value.

Re: Round numbers (%) in expression

swuehl — Wed, 28 Dec 2016 12:54:34 GMT

You would need to set the format for your expression ( Sum({<DATUM_ID = ....), not for the expression attribute 'Show Value'.

Hence add the Num() part to your original expression or set the format accordingly on number tab.

Re: Round numbers (%) in expression

sunny_talwar — Wed, 28 Dec 2016 12:54:59 GMT

Show Value just specify which value to show, but to format you either need to format your actual expression using Num() function or go to Numbers tab to format it.

Also, show value should work with this only

Rank(Sum({<DATUM_ID = {">=$(=datum_od) <=$(=datum_do)"}>} PRIJATE)/ Sum(TOTAL <AREA> {<DATUM_ID = {">=$(=datum_od) <=$(=datum_do)"}>} PRIJATE)) = 1

Re: Round numbers (%) in expression

Wed, 28 Dec 2016 13:04:43 GMT

Thank you Sunny. You are right.

Show Value work only with this:

Rank(Sum({<DATUM_ID = {">=$(=datum_od) <=$(=datum_do)"}>} PRIJATE)/ Sum(TOTAL <AREA> {<DATUM_ID = {">=$(=datum_od) <=$(=datum_do)"}>} PRIJATE)) = 1

and in Number tab I add Format Pattern 0%. Now this is according to my idea.