Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
rdsuperlike
Creator
Creator

Number format: Million and kilos integrating together in one expression

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

1 Solution

Accepted Solutions
julian_rodriguez
Partner - Specialist
Partner - Specialist

Hello

Try this:

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

Regards

View solution in original post

10 Replies
julian_rodriguez
Partner - Specialist
Partner - Specialist

Hello

Try this:

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

Regards

rdsuperlike
Creator
Creator
Author

Thanks!

It works but can you explain it once.

julian_rodriguez
Partner - Specialist
Partner - Specialist

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!

rdsuperlike
Creator
Creator
Author

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

julian_rodriguez
Partner - Specialist
Partner - Specialist

It's just a shorter way to do this:

If(Sum(Sales) < 1000000, Sum(Sales)/1000, Sum(Sales)/1000000)

Regards

rdsuperlike
Creator
Creator
Author

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

julian_rodriguez
Partner - Specialist
Partner - Specialist

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

rdsuperlike
Creator
Creator
Author

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

rdsuperlike
Creator
Creator
Author

If I want to show O for null values how to integrate to above equation. Right now it shows 0.000K or 0.000M