17 Replies Latest reply: Dec 14, 2015 8:55 AM by bruno bertels

# Number formatting and Decimal Point

Hi all,

The below expression gives me the decimal output as (5.4%) when i used NumberFormatting---> Number ---> single decimal point(1.1%)

But how can i achieve this using Number formatting as auto.

sum({\$<CanonicalDate={">=\$(=date(Max(CanonicalDate)-30))<\$(=date(Max(CanonicalDate)))"}, DateType={'completion'}>}course_completed)

/

count({\$<CanonicalDate={">=\$(=date(Max(CanonicalDate)-30))<\$(=date(Max(CanonicalDate)))"}, DateType= {'invitation'}>} invitation_id)

I Tried Round, Ceil , and Floor  but i am getting different values.

And also i tried with

Num(sum({\$<CanonicalDate={">=\$(=date(Max(CanonicalDate)-30))<\$(=date(Max(CanonicalDate)))"}, DateType={'completion'}>}course_completed)

/

count({\$<CanonicalDate={">=\$(=date(Max(CanonicalDate)-30))<\$(=date(Max(CanonicalDate)))"}, DateType= {'invitation'}>} invitation_id),'#,##0.00%', '#,##0')

How can i achieve this requirement?

Thanks,

Pramod

• ###### Re: Number formatting and Decimal Point

Num(sum({\$<CanonicalDate={">=\$(=date(Max(CanonicalDate)-30))<\$(=date(Max(CanonicalDate)))"}, DateType={'completion'}>} course_completed)

/

count({\$<CanonicalDate={">=\$(=date(Max(CanonicalDate)-30))<\$(=date(Max(CanonicalDate)))"}, DateType= {'invitation'}>} invitation_id),'0.0%')

• ###### Re: Number formatting and Decimal Point

Is it the right way of doing calculation ?

(5.4%-5-5%)/5.4%=?

Is the below expression is corrrect for above calculation?

(Num(sum({\$<CanonicalDate={">=\$(=date(Max(CanonicalDate)-30))<\$(=date(Max(CanonicalDate)))"}, DateType= {'completion'}>} course_completed)/

count({\$<CanonicalDate={">=\$(=date(Max(CanonicalDate)-30))<\$(=date(Max(CanonicalDate)))"}, DateType= {'invitation'}>} invitation_id),'0.0%')

-

num(sum({\$<CanonicalDate={">=\$(=date(Max(CanonicalDate)-60))<\$(=date(Max(CanonicalDate)-30))"}, DateType= {'completion'}>} course_completed)/

count({\$<CanonicalDate={">=\$(=date(Max(CanonicalDate)-60))<\$(=date(Max(CanonicalDate)-30))"}, DateType= {'invitation'}>} invitation_id),'0.0%'))

/

(Num(sum({\$<CanonicalDate={">=\$(=date(Max(CanonicalDate)-30))<\$(=date(Max(CanonicalDate)))"}, DateType= {'completion'}>} course_completed)/

count({\$<CanonicalDate={">=\$(=date(Max(CanonicalDate)-30))<\$(=date(Max(CanonicalDate)))"}, DateType= {'invitation'}>} invitation_id),'0.0%'))

• ###### Re: Number formatting and Decimal Point

Hi Pramod,

Give number format for the final number, instead of giving for every expression

Like Num((5.4%-5-5%)/5.4%, '0.0%').

(Num(sum({\$<CanonicalDate={">=\$(=date(Max(CanonicalDate)-30))<\$(=date(Max(CanonicalDate)))"}, DateType= {'completion'}>} course_completed)/

count({\$<CanonicalDate={">=\$(=date(Max(CanonicalDate)-30))<\$(=date(Max(CanonicalDate)))"}, DateType= {'invitation'}>} invitation_id)

-

sum({\$<CanonicalDate={">=\$(=date(Max(CanonicalDate)-60))<\$(=date(Max(CanonicalDate)-30))"}, DateType= {'completion'}>} course_completed)/

count({\$<CanonicalDate={">=\$(=date(Max(CanonicalDate)-60))<\$(=date(Max(CanonicalDate)-30))"}, DateType= {'invitation'}>} invitation_id))

/

(sum({\$<CanonicalDate={">=\$(=date(Max(CanonicalDate)-30))<\$(=date(Max(CanonicalDate)))"}, DateType= {'completion'}>} course_completed)/

count({\$<CanonicalDate={">=\$(=date(Max(CanonicalDate)-30))<\$(=date(Max(CanonicalDate)))"}, DateType= {'invitation'}>} invitation_id),'0.0%'))

• ###### Re: Number formatting and Decimal Point

If i do through calci i will get (5.4%-5-5%)/5.4%= -1.8518

But same i tried through expression the result is -0.7.

Where i am doing wrong?

• ###### Re: Number formatting and Decimal Point

For me its seems, you need to close couple of more brackets, try my suggested expression.

• ###### Re: Number formatting and Decimal Point

Hi,

Because of rounding the number may change slightly, check the values of each expression in text object and calculate the value, this way you can verify the numbers.

Regards,

jagan.

• ###### Re: Number formatting and Decimal Point

Hi jagan,

Any help over this,

As when i used a single expression it is rounding to decimal point like 0.054 but when i used same expression with other expressions for calculations it's not working.

Any suggestions to achieve the needed.

Thanks,

Pramod

• ###### Re: Number formatting and Decimal Point

try like this:

Num(

(sum({\$<CanonicalDate={">=\$(=date(Max(CanonicalDate)-30))<\$(=date(Max(CanonicalDate)))"}, DateType= {'completion'}>} course_completed)/

count({\$<CanonicalDate={">=\$(=date(Max(CanonicalDate)-30))<\$(=date(Max(CanonicalDate)))"}, DateType= {'invitation'}>} invitation_id))

-

(sum({\$<CanonicalDate={">=\$(=date(Max(CanonicalDate)-60))<\$(=date(Max(CanonicalDate)-30))"}, DateType= {'completion'}>} course_completed)/

count({\$<CanonicalDate={">=\$(=date(Max(CanonicalDate)-60))<\$(=date(Max(CanonicalDate)-30))"}, DateType= {'invitation'}>} invitation_id)))

/

(sum({\$<CanonicalDate={">=\$(=date(Max(CanonicalDate)-30))<\$(=date(Max(CanonicalDate)))"}, DateType= {'completion'}>} course_completed)/

count({\$<CanonicalDate={">=\$(=date(Max(CanonicalDate)-30))<\$(=date(Max(CanonicalDate)))"}, DateType= {'invitation'}>} invitation_id))

,'0.0%')

• ###### Re: Number formatting and Decimal Point

I Tried above also not working.

You check with below expression:

i will call below expression as "A"

Num(sum({\$<CanonicalDate={">=\$(=date(Max(CanonicalDate)-30))<\$(=date(Max(CanonicalDate)))"}, DateType= {'completion'}>} course_completed)/

count({\$<CanonicalDate={">=\$(=date(Max(CanonicalDate)-30))<\$(=date(Max(CanonicalDate)))"}, DateType= {'invitation'}>} invitation_id),'0.000')

Gives output as A=0.054

i will call below expression as "B"

Num(sum({\$<CanonicalDate={">=\$(=date(Max(CanonicalDate)-60))<\$(=date(Max(CanonicalDate)-30))"}, DateType= {'completion'}>} course_completed)/

count({\$<CanonicalDate={">=\$(=date(Max(CanonicalDate)-60))<\$(=date(Max(CanonicalDate)-30))"}, DateType= {'invitation'}>} invitation_id),'0.000')

Gives output as B=0.055

A-B = -0.36m

When i debug i am getting value like above please go through once

• ###### Re: Number formatting and Decimal Point

well, I would suggest break your A expression and check one by one while values are matching or not...

like:

First check the value of this expression only

sum({\$<CanonicalDate={">=\$(=date(Max(CanonicalDate)-30))<\$(=date(Max(CanonicalDate)))"}, DateType= {'completion'}>} course_completed)

Then check value of following expression only:

count({\$<CanonicalDate={">=\$(=date(Max(CanonicalDate)-30))<\$(=date(Max(CanonicalDate)))"}, DateType= {'invitation'}>} invitation_id)

Hope this will help!

• ###### Re: Number formatting and Decimal Point

Yes the output of A matches when tried breaking the A into two.

0.054 and 0.055 is the correct value.

0.054 - 0.055 should be -0.001 but i am getting something like -0.0003636

I am getting wrong only while doing A-B

• ###### Re: Number formatting and Decimal Point

Okay, can we try like this:

num((num(A,'0.0000')-num(B,'0.0000')),'0.0000')

• ###### Re: Number formatting and Decimal Point

No balraj, i already tried with this expression it's getting the same value itself..

• ###### Re: Number formatting and Decimal Point

Quite Strange.

Any sample data/QVDs?

• ###### Re: Number formatting and Decimal Point

In above image you can observe the count in textbox without num(), while doing subtraction it is using the 0.0542279... value how can i restrict that it should use only 0.054. Even i used num() for each expression also it's using the complete value.

• ###### Re: Number formatting and Decimal Point

Any suggestions over this?

• ###### Re: Number formatting and Decimal Point

Hi

Floor( 3.88 , 0.1 ) =  will give 3.8.

so try

floor(yourmesure,step) - floor(yourmesure,step)

bruno