Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator II
Creator 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
Highlighted
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

View solution in original post

11 Replies
Highlighted
Specialist
Specialist

Re: Rounding in Qlikview

You can try the Round function.

 

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

Highlighted
Creator II
Creator 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%.

Highlighted
MVP & Luminary
MVP & Luminary

Re: Rounding in Qlikview

Take a look here: Rounding-Errors.

- Marcus

Highlighted

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
Highlighted
Creator II
Creator 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')

Highlighted

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


Highlighted
Creator II
Creator II

Re: Rounding in Qlikview

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

Highlighted
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

Highlighted
Creator II
Creator II

Re: Rounding in Qlikview

Hi,

 

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