# QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Highlighted
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

Tags (3)
1 Solution

Accepted Solutions
Partner

## 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

9 Replies
Partner

## 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

Contributor

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

Thanks!

It works but can you explain it once.

Partner

## 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!

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

Partner

## 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

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

Partner

## 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

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

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