Number format

[rdsuperlike]

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.

[jagan · Accepted solution]

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.

[rdsuperlike]

It works.

Can you please explain it to me?

[jagan]

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.

[#5]

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

[PrashantSangle]

Hi,

Use Round()

try like

Round(Value,'0.1')

Regards

[jagan]

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.

[#8]

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