Qlik Community

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

17 Replies
MVP
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

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

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

balrajahlawat
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?


balrajahlawat
Esteemed Contributor

Re: Number formatting and Decimal Point

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

Hope will help you!

MVP
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

balrajahlawat
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!