topic rounding in QlikView

rounding

surajap123 — Wed, 25 Nov 2020 16:16:04 GMT

Hi Experts,

I have this excel rounding expression. I want to use the same logic in qlikview.

If(Round(B10,4)> (Round(A10,4)-Round(A10,4)*0.005)),'N')

The column B has Feb sales and column A has Jan sales in excel.

Please help!

Re: rounding

ravikumar_iyana — Thu, 28 Sep 2017 01:19:13 GMT

Hi,

Up to my knowledge plz don't take round in sales field

After data load , in writing expression time show total option is there write

There based on your requirement Ceil() , Floor(),

Like this apply

Re: rounding

its_anandrjs — Thu, 28 Sep 2017 04:21:02 GMT

Can you share some sample data here to have a look.

Re: rounding

Anil_Babu_Samineni — Thu, 28 Sep 2017 04:29:57 GMT

This is straight forward

If(Round(FieldnameofB10,4) > (Round(FieldnameofA10,4)-Round(FieldnameofA10,4)*0.005),'N')

Re: rounding

its_anandrjs — Thu, 28 Sep 2017 05:14:52 GMT

Follow the bodmos rule in formula and try this replace your fields there

=If( Round(FieldB,4) > ( Round(FieldA,4) - ( Round(FieldA,4) * 0.005 ) ),'N')

Re: rounding

avinashelite — Thu, 28 Sep 2017 05:55:55 GMT

how you have imported the data to Qlikview I mean Feb and Jan sales are in different columns or in the same columns ? based on this the expression needs to be tweaked

Re: rounding

surajap123 — Thu, 28 Sep 2017 06:45:19 GMT

Thanks All!

This both jan and feb data is in single column in qlikview which is sales percentage.

In front end, I want to compare both month sales in chart by rounding it to 4 decimals and then smoothing it to 0.005(smoothing purpose is- if difference between both percentage is less than half percent then consider both as same). This is my understanding.

Below is the excel formula. Please help how can i write in qlikview.

excel->

If(Round(B10,4)> (Round(A10,4)-Round(A10,4)*0.005)),'N')

qlikview expression-

If(Round(B10,0.0001)> (Round(A10,0.0001)-Round(A10,0.0001)*0.005)),'N') //Is this correct??

Re: rounding

its_anandrjs — Thu, 28 Sep 2017 06:51:43 GMT

In B10 and A10 what is the value for i believe it is for the sales if so please explain with example.

Re: rounding

surajap123 — Thu, 28 Sep 2017 07:19:52 GMT

sum({<Month = {'Jan'}>}field1)/sum({<Month = {'Jan'}>}field2) //A10

sum({<Month = {'Feb'}>}field1)/sum({<Month = {'Feb'}>}field2) //B10

Re: rounding

its_anandrjs — Thu, 28 Sep 2017 07:27:56 GMT

Try this

=If( Round(FieldB,4) > ( Round(FieldA,4) - ( Round(FieldA,4) * 0.005 ) ),'N')

This is in correct

If(Round(B10,0.0001)> (Round(A10,0.0001)-Round(A10,0.0001)*0.005)),'N') //Is this correct??

Re: rounding

effinty2112 — Thu, 28 Sep 2017 07:38:23 GMT

Hi Suraj,

First a little simplification

If(Round(B10,4)> (Round(A10,4)-Round(A10,4)*0.005)),'N')

If(Round(B10,4)> 0.995*(Round(A10,4))),'N')

If B10 = [Feb Sales] and A10 = [Jan Sales] then

If(Round([Feb Sales],0.0001) > Round(0.995*[Jan Sales],0.0001),'N')

It's a bit odd to compare two rounded figures, are you sure we can't just use

If([Feb Sales] > 0.995*[Jan Sales],'N') ?

Suggest it's best to use an aggregation function so

If(sum([Feb Sales]) > 0.995*sum([Jan Sales]),'N')

Good luck

Andrew

Re: rounding

surajap123 — Thu, 28 Sep 2017 08:00:32 GMT

‌I am smoothing by half percenta ie. 0.5/100 = 0.005.

MSy i know what is 0.995 in your expression.

Re: rounding

dinuwanbr — Thu, 28 Sep 2017 09:13:00 GMT

Hi,

You can you the same instead of B10(Cell Value in excel) give the QlikView Field Name.

For A10 also the same.

If(Round(B10,4)> (Round(A10,4)-Round(A10,4)*0.005)),'N')

Rgds,

Tharindu