topic Re: Number format in QlikView

Number format

rdsuperlike — Mon, 11 May 2015 04:05:37 GMT

I want to restrict my display to 4 digits irrespective of right side of "." or left

Ex: 1.000M

1.234M

10.25M

999.9M

1.000K

22.24K

222.5K

How should I incorporate in below expression?

Num(Sum(Sales)/ if(Sum(Sales)<1e6,1e3,1e6) ,if(Sum(Sales)<1e6,'#.##0,0 k','#.##0,0 M'))

Any help is appreciated.

Re: Number format

jagan — Mon, 11 May 2015 04:21:47 GMT

Hi,

Try this

=Left(Num(Sum(Sales)/if(Sum(Sales) >= 1000000, 1000000, 1000), '#.000'), 5) & if(Sum(Sales) >= 1000000, 'M', 'K')

Hope this helps you.

Regards,

Jagan.

Re: Number format

rdsuperlike — Mon, 11 May 2015 04:47:58 GMT

It works.

Can you please explain it to me?

Re: Number format

jagan — Mon, 11 May 2015 05:25:37 GMT

HI,

if(Sum(Sales) >= 1000000, 1000000, 1000) - If Sales is greater than 1 Million dividing sales with million else thousand.

Num(Sum(Sales)/if(Sum(Sales) >= 1000000, 1000000, 1000), '#.000') - Formatting the number with 3 decimal places

Left(Num(Sum(Sales)/if(Sum(Sales) >= 1000000, 1000000, 1000), '#.000'), 5) - Getting the left most 5 digits using Left function

& if(Sum(Sales) >= 1000000, 'M', 'K') - Concatenating the symbol to the number .

Hope this helps you.

Regards,

Jagan.

Re: Number format

Wed, 09 Dec 2015 04:27:53 GMT

Hi jagan,

jagan‌

I used above expression can you help where i am going wrong?

Left(Num(Count({$<DateType={accessed}>}invitation_id)/if(Count({$<DateType={accessed}>}invitation_id) >= 1000000, 1000000, 1000), '#.000'), 5) & if(Count({$<DateType={accessed}>}invitation_id) >= 1000000, 'M', 'K')

My actual expression is :Count({$<DateType={accessed}>}invitation_id)

181.8 k

16.45 k

All i need is i need only one decimal point not 2 decimal point. i.e 16.45 must be truncated to nearest decimal point 16.5

And is their any way to capitalize and bold the 'K'.

Regards,

Pramod

Re: Number format

PrashantSangle — Wed, 09 Dec 2015 04:43:23 GMT

Hi,

Use Round()

try like

Round(Value,'0.1')

Regards

Re: Number format

jagan — Wed, 09 Dec 2015 04:55:27 GMT

Hi,

Format the number in Num() like below for one decimal

Left(Num(Count({$<DateType={accessed}>}invitation_id)/if(Count({$<DateType={accessed}>}invitation_id) >= 1000000, 1000000, 1000), '#.0'), 5) & if(Count({$<DateType={accessed}>}invitation_id) >= 1000000, 'M', 'K')

Also it is not possible to apply Bold to one character (M or K).

Regards,

Jagan.

Re: Number format

Fri, 11 Dec 2015 12:16:34 GMT

Hi jagan,

Please suggest if i am doing this as complicated or the way i am working is correct?

I am using a below expression to get % in a straight table. It's a very big i think so...

The code below which is in bold is using to get the % value for example (5.8%-5.6%)/5.8%= 3.4%.

For this column i am using Number Formatting---> Number ---> one decimal point(1.3%)

For all others it's working how can i get the one decimal point for the bold expression.

I used Ceil, Floor and round and also dynamic formatting. it's not working please help on it.

Num(if(Metrics='Invitations',((count({$<CanonicalDate={">=$(=date(Max(CanonicalDate)-30))<$(=date(Max(CanonicalDate)))"}, DateType= {'invitation'}

>} invitation_id))

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

>} invitation_id)))

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

>} invitation_id)),

if(Metrics='Landing Page Hits',((count({$<CanonicalDate={">=$(=date(Max(CanonicalDate)-30))<$(=date(Max(CanonicalDate)))"}, DateType= {'accessed'}

>} invitation_id))

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

>} invitation_id)))

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

>} invitation_id)),

if(Metrics='Sign-Ups',((sum({$<CanonicalDate={">=$(=date(Max(CanonicalDate)-30))<$(=date(Max(CanonicalDate)))"}, DateType= {'signed'}

>} user_signed))

(sum({$<CanonicalDate={">=$(=date(Max(CanonicalDate)-60))<$(=date(Max(CanonicalDate)-30))"}, DateType= {'signed'}

>} user_signed)))

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

>} user_signed)),

if(Metrics='Completions',((sum({$<CanonicalDate={">=$(=date(Max(CanonicalDate)-30))<$(=date(Max(CanonicalDate)))"}, DateType= {'completion'}

>} course_completed))

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

>} course_completed)))

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

>} course_completed)),

if(Metrics='Completions Rate',(Ceil(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.01)

Ceil(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.01))

(Ceil(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.01))

))))),'#,##0.00%', '#,##0')