Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Put the numbers at the same scale

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 :

Capture.PNGBut 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??

1 Solution

Accepted Solutions
settu_periasamy
Master III
Master III

May be try this

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

        IF(SUM(FIELD1) >= 10000000 ,

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

View solution in original post

4 Replies
robert_mika
Master III
Master III

Try to use Replace() to take off the comas

Trim only takes out empty spaces

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

settu_periasamy
Master III
Master III

May be try this

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

        IF(SUM(FIELD1) >= 10000000 ,

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

Not applicable
Author

Settu_periasmy,

Thanks You just keep it simple

settu_periasamy
Master III
Master III

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