Rounding in Qlikview

[dunnalahk123]

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.

[jwjackso]

You can try the Round function.

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

[dunnalahk123]

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%.

[marcus_sommer]

Take a look here: Rounding-Errors.

- Marcus

[rubenmarin]

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]

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')

[rubenmarin]

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]

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

[marcus_sommer]

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]

Hi,

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