Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Highlighted
rdsuperlike
Contributor

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

Re: Number format: Million and kilos integrating together in one expression

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

9 Replies

Re: Number format: Million and kilos integrating together in one expression

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

rdsuperlike
Contributor

Re: Number format: Million and kilos integrating together in one expression

Thanks!

It works but can you explain it once.

Re: Number format: Million and kilos integrating together in one expression

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
Contributor

Re: Number format: Million and kilos integrating together in one expression

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

Re: Number format: Million and kilos integrating together in one expression

It's just a shorter way to do this:

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

Regards

rdsuperlike
Contributor

Re: Number format: Million and kilos integrating together in one expression

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

Re: Number format: Million and kilos integrating together in one expression

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
Contributor

Re: Number format: Million and kilos integrating together in one expression

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
Contributor

Re: Number format: Million and kilos integrating together in one expression

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