Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted

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

10 Replies
Highlighted

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

Highlighted
Creator
Creator

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

Thanks!

It works but can you explain it once.

Highlighted

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!

Highlighted
Creator
Creator

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

Highlighted

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

Highlighted
Creator
Creator

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

Highlighted

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

Highlighted
Creator
Creator

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

Highlighted
Creator
Creator

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