Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I want to integrate the below two expressions into one expression.
What I want is if < 1M it should give in k, but if more than 1M it should give in M.
Current expression are as below:
Below expression results into 0.0M if result is less than a million
=text(Num(SUM(QTY)/1000000, '#.#M'))
Below expression results into 3087.3k even when it is more than million
=text(Num(SUM(QTY)/1000, '#.#k'))
Ny Help is appreciated.
Thanks
Hello
Try this:
Num(Sum(Sales)/ if(Sum(Sales)<1e6,1e3,1e6) ,if(Sum(Sales)<1e6,'#.##0,0 k','#.##0,0 M'))
Regards
Hello
Try this:
Num(Sum(Sales)/ if(Sum(Sales)<1e6,1e3,1e6) ,if(Sum(Sales)<1e6,'#.##0,0 k','#.##0,0 M'))
Regards
Thanks!
It works but can you explain it once.
Sure:
The Num() gives the format, on this case it selects between '#.##0,0 k' when Sum(Sales) is not greater than 1e6 (btw 1e6 it's the same than 1000000) or '#.##0,0 M' when it's greater than 1e6.
Besides, you need to evaluate if you divide by 1e3 (1000) or 1e6 (1000000) according to the Sum(Sales) value.
I hope i have been clear, my english is not the best.
Regards!
Thanks once again.
I got that part. But i didnt understand why did u use below bold part in the expression. What is the significance of that?
Num(Sum(Sales)/ if(Sum(Sales)<1e6,1e3,1e6) ,if(Sum(Sales)<1e6,'#.##0,0 k','#.##0,0 M'))
It's just a shorter way to do this:
If(Sum(Sales) < 1000000, Sum(Sales)/1000, Sum(Sales)/1000000)
Regards
Ok. Makes sense. Then why do we require Sum(Sales)<1e6 in second if. I am just getting confused bcz of two ifs
Num(Sum(Sales)/ if(Sum(Sales)<1e6,1e3,1e6) ,if(Sum(Sales)<1e6,'#.##0,0 k','#.##0,0 M'))
Sure, the fist if is in order to get the correct value (thousands or Millions)
the second if, is in order to get the correct Num format (k or M)
I hope to be clear
Thanks for detailed explanation.
I also 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'))
If I want to show O for null values how to integrate to above equation. Right now it shows 0.000K or 0.000M