Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

17 Replies
jonathandienst
Partner - Champion III
Partner - Champion III

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable
Author

Thanks For the reply,

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

jagan
Luminary Alumni
Luminary Alumni

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

Anonymous
Not applicable
Author

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

Not applicable
Author

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?


Anonymous
Not applicable
Author

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

Hope will help you!

jagan
Luminary Alumni
Luminary Alumni

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.

Not applicable
Author

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

Anonymous
Not applicable
Author

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!