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

Number format: Million and kilos integrating together in one expression

rdsuperlike — Fri, 08 May 2015 19:23:21 GMT

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

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

julian_rodriguez — Fri, 08 May 2015 19:33:13 GMT

Hello

Try this:

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

Regards

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

rdsuperlike — Fri, 08 May 2015 20:52:12 GMT

Thanks!

It works but can you explain it once.

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

julian_rodriguez — Fri, 08 May 2015 21:11:29 GMT

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!

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

rdsuperlike — Fri, 08 May 2015 21:22:42 GMT

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

julian_rodriguez — Fri, 08 May 2015 21:28:48 GMT

It's just a shorter way to do this:

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

Regards

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

rdsuperlike — Fri, 08 May 2015 21:35:19 GMT

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

julian_rodriguez — Fri, 08 May 2015 21:42:08 GMT

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

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

rdsuperlike — Fri, 08 May 2015 23:05:33 GMT

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

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

rdsuperlike — Thu, 04 Jun 2015 19:13:43 GMT

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

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

lbunnell — Tue, 26 Nov 2019 13:26:22 GMT

This worked in a straight table for me, but not in a bar chart which views the 1.24 million as smaller than 43.71 thousand. Anyone know how to make this work for Bar Charts?