topic Re: Rounding in Qlikview in QlikView

Rounding in Qlikview

dunnalahk123 — Sat, 16 Nov 2024 03:31:34 GMT

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.

Re: Rounding in Qlikview

jwjackso — Mon, 20 May 2019 22:08:54 GMT

You can try the Round function.

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

Re: Rounding in Qlikview

dunnalahk123 — Tue, 21 May 2019 08:03:08 GMT

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

Re: Rounding in Qlikview

marcus_sommer — Tue, 21 May 2019 08:12:25 GMT

Take a look here: Rounding-Errors.

- Marcus

Re: Rounding in Qlikview

rubenmarin — Tue, 21 May 2019 08:26:10 GMT

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

Re: Rounding in Qlikview

dunnalahk123 — Mon, 10 Jun 2019 16:37:07 GMT

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

rubenmarin — Mon, 10 Jun 2019 19:08:42 GMT

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

Re: Rounding in Qlikview

dunnalahk123 — Tue, 11 Jun 2019 06:23:01 GMT

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

Re: Rounding in Qlikview

marcus_sommer — Tue, 11 Jun 2019 07:25:54 GMT

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

Re: Rounding in Qlikview

dunnalahk123 — Tue, 11 Jun 2019 09:48:13 GMT

Hi,

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

Re: Rounding in Qlikview

marcus_sommer — Tue, 11 Jun 2019 10:10:42 GMT

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

Re: Rounding in Qlikview

dunnalahk123 — Tue, 11 Jun 2019 10:17:51 GMT

Hi Marcus,

Excellent, it works 🙂 Thank You so Much .