topic Put the numbers at the same scale in QlikView

Put the numbers at the same scale

Mon, 25 Jan 2016 15:34:39 GMT

Hi,

I've a large amount of datas with different formats and I want that all numbers have the same format. I know that exists the option above :

But I want All the number in Million (K). Because I have metrics in BILLION and other in Million and to show both in same chart it doesn't make the analysis simple...

I am trying to use a expression like:

=IF(LEN(TRIM(SUM(FIELD1)))> 6 AND

LEN(TRIM(SUM(FIELD1)))< 9,

SUM(FIELD1)/1000,

if(LEN(TRIM(SUM(FIELD1)))> 8 AND

LEN(TRIM(SUM(FIELD1)))< 11,

SUM(FIELD1)/1000000,

SUM( FIELD1)))

But this expression counts the 'commas' too so it is not very effective

There exists a better way to do this??

Re: Put the numbers at the same scale

robert_mika — Mon, 25 Jan 2016 15:47:25 GMT

Try to use Replace() to take off the comas

Trim only takes out empty spaces

LEN(TRIM(REPLACE(SUM(FIELD1),',','')))> 6....

Re: Put the numbers at the same scale

settu_periasamy — Mon, 25 Jan 2016 16:16:20 GMT

May be try this

=if(SUM(FIELD1) >= 1000000000, SUM(FIELD1)/1000000,

IF(SUM(FIELD1) >= 10000000 ,

SUM(FIELD1)/1000, SUM(FIELD1)))

Re: Put the numbers at the same scale

Mon, 25 Jan 2016 16:32:34 GMT

Settu_periasmy,

Thanks You just keep it simple

Re: Put the numbers at the same scale

settu_periasamy — Mon, 25 Jan 2016 16:42:41 GMT

if it is worked can you mark it as correct or helpful and close this thread..