Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
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.
It works.
Can you please explain it to me?
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.
Hi 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
Hi,
Use Round()
try like
Round(Value,'0.1')
Regards
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.
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')