New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

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

Tags (2)
17 Replies
MVP

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

Not applicable

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

MVP

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

Esteemed Contributor

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

Not applicable

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?

Esteemed Contributor

Re: Number formatting and Decimal Point

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

MVP

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.

Not applicable

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

Esteemed Contributor

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!