Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
dunnalahk123
Contributor II

Rounding in Qlikview

HI,

 

i am writing some expression as per below

 

=num ( SUM ({<ScenarioLabel={'Actuals'},NumValue) /
SUM ({<ScenarioLabel={'Actuals'}>} DenValue), '0.#%')

 

its normal value is 78.26% which is good it is rounding to 78.3%

 

but same expression if value is 75.75% it is not rounding to 75.8 .. can some one help me here .

 

problem is after decimal if it is greater than 5 it is rounding if it is equal to 5 it is not rounding as per above example.

Labels (1)
1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

Re: Rounding in Qlikview

The rounding happens not on the right digits because it should round to the third digit and not the first, therefore change it to:

num(round(round(Expr1, 0.001) - round(Expr2, 0.001), 0.001), YourFormat)

- Marcus

11 Replies
jwjackso
Valued Contributor

Re: Rounding in Qlikview

You can try the Round function.

 

=num (ROUND( SUM ({<ScenarioLabel={'Actuals'},NumValue) /
SUM ({<ScenarioLabel={'Actuals'}>} DenValue),.1), '0.#%')

dunnalahk123
Contributor II

Re: Rounding in Qlikview

HI,

No, its giving me 80 % but i need only 75.8 % .

as i mention in my post if after decimal if it is greater than 5 it is rounding it correct but if it equal to 5 it is giving me 75.75 gives 75.7 but it should be 75.8%.

MVP & Luminary
MVP & Luminary

Re: Rounding in Qlikview

Take a look here: Rounding-Errors.

- Marcus

Re: Rounding in Qlikview

Hi, maybe the real value is 75.749 or something similar, rounded to 2 decimals is 75.75 but rounded to 1 decimal is 75.7
dunnalahk123
Contributor II

Re: Rounding in Qlikview

Hi,

 

Problem here is my Q1 value is 75.7 (which actual value is 75.75xxxxxxxxx) but when i round to one decimal it is showing my front end report as 75.7

=num ( SUM ({<ScenarioLabel={'Actuals'},Scope={'DC'},Subregion={$(vGeo)},Subregion1= {$(vGeo1)},Subregion2={$(vGeo2)},Country={$(vCountry)},[Quarter]={$(vFYQ_1Final)}>} NumValue) /
SUM ({<ScenarioLabel={'Actuals'},Scope={'DC'},Subregion={$(vGeo)},Subregion1= {$(vGeo1)},Subregion2={$(vGeo2)},Country={$(vCountry)},[Quarter]={$(vFYQ_1Final)}>} DenValue), '0.#%')

and my Q2 value is 78.3 (which actual value is 78.26xxxxxxxxx) but when i round to one decimal it is showing my front end report as 78.3

=num ( SUM ({<ScenarioLabel={'Actuals'},Scope={'DC'},Subregion={$(vGeo)},Subregion1= {$(vGeo1)},Subregion2={$(vGeo2)},Country={$(vCountry)},[Quarter]={$(vFYQ_2Final)}>} NumValue) /
SUM ({<ScenarioLabel={'Actuals'},Scope={'DC'},Subregion={$(vGeo)},Subregion1= {$(vGeo1)},Subregion2={$(vGeo2)},Country={$(vCountry)},[Quarter]={$(vFYQ_2Final)}>} DenValue), '0.#%')

So when I do my QoQ Calculation Q2-Q1 which is 78.3 - 75.7  i should get 2.6 . but internally Qlikview is reading like 78.26xxxxx - 75.75xxxx which gives me 2.5 actually right.

but i need to take exact value of Q1 and Q2 and give me 2.6 as my QoQ

 

this is what i wrote for QoQ

NUM(
(

(


(
num ( SUM ({<ScenarioLabel={'Actuals'},Scope={'DC'},Subregion={$(vGeo)},Subregion1= {$(vGeo1)},Subregion2={$(vGeo2)},Country={$(vCountry)},[Quarter]={$(vFYQ_1Final)}>} NumValue) /
SUM ({<ScenarioLabel={'Actuals'},Scope={'DC'},Subregion={$(vGeo)},Subregion1= {$(vGeo1)},Subregion2={$(vGeo2)},Country={$(vCountry)},[Quarter]={$(vFYQ_1Final)}>} DenValue), '0.#%') )-


(
num ( SUM ({<ScenarioLabel={'Actuals'},Scope={'DC'},Subregion={$(vGeo)},Subregion1= {$(vGeo1)},Subregion2={$(vGeo2)},Country={$(vCountry)},[Quarter]={$(vFYQ_2Final)}>} NumValue) /
SUM ({<ScenarioLabel={'Actuals'},Scope={'DC'},Subregion={$(vGeo)},Subregion1= {$(vGeo1)},Subregion2={$(vGeo2)},Country={$(vCountry)},[Quarter]={$(vFYQ_2Final)}>} DenValue), '0.#%'))




)


*100)

,'0.#pts')

Re: Rounding in Qlikview

Try using round() to use only one decimal:

Round(num ( SUM ({<ScenarioLabel={'Actuals'},Scope={'DC'},Subregion={$(vGeo)},Subregion1= {$(vGeo1)},Subregion2={$(vGeo2)},Country={$(vCountry)},[Quarter]={$(vFYQ_1Final)}>} NumValue) /
SUM ({<ScenarioLabel={'Actuals'},Scope={'DC'},Subregion={$(vGeo)},Subregion1= {$(vGeo1)},Subregion2={$(vGeo2)},Country={$(vCountry)},[Quarter]={$(vFYQ_1Final)}>} DenValue), '0.#%'), 0.1) )-Round(num ( SUM ({<ScenarioLabel={'Actuals'},Scope={'DC'},Subregion={$(vGeo)},Subregion1= {$(vGeo1)},Subregion2={$(vGeo2)},Country={$(vCountry)},[Quarter]={$(vFYQ_2Final)}>} NumValue) /
SUM ({<ScenarioLabel={'Actuals'},Scope={'DC'},Subregion={$(vGeo)},Subregion1= {$(vGeo1)},Subregion2={$(vGeo2)},Country={$(vCountry)},[Quarter]={$(vFYQ_2Final)}>} DenValue), '0.#%'), 0.1)

Also note the article linked by Marcus, conversion on decimal values between decimal and binary can lead to slight differences y result


dunnalahk123
Contributor II

Re: Rounding in Qlikview

Hi, I am getting zero i fi give your expression.

MVP & Luminary
MVP & Luminary

Re: Rounding in Qlikview

Probably there is now just a small syntax issue with a missing bracket, comma or something like this ... In the end your expression should be look like:

num(round(round(Expr1, 0.1) - round(Expr2, 0.1), 0.1), YourFormat)

Therefore just try it again.

- Marcus

dunnalahk123
Contributor II

Re: Rounding in Qlikview

Hi,

 

I am sorry still i see same, could you check i am attaching my app.