sorting problem

[krishna20]

Hi friends ,

I'm facing  sorting problem for calculated dimension.The below i mentioned the calculated dimension and the resulting one.

=if(CS_AMT_LC_1<0 ,'<0',

if(CS_AMT_LC_1>1 and CS_AMT_LC_1<=500000,'1-500,000',

if(CS_AMT_LC_1>=500001 and CS_AMT_LC_1<=1000000,'500,001-1,000,000',

if(CS_AMT_LC_1>=1000001 and CS_AMT_LC_1<=2000000,'1,000,001-2,000,000',

if(CS_AMT_LC_1>=2000000 and CS_AMT_LC_1<=5000000,'2,000,001-5,000,000',

if(CS_AMT_LC_1>=5000001 and CS_AMT_LC_1<=10000000,'5,000,001-10,000,000',

if(CS_AMT_LC_1>10000000,'Above 10,000,000')))))))

Resulting one

<0

1- 500,000

1,000,001 - 2,000,000

2,000,001 - 5,000,000

5,000,001 - 10,000,000

500 ,001 - 1,000,000

Above 10,000,000

Please suggest me how to resolve this.

Regards

Krishna

[anbu1984 · Accepted solution]

=if(CS_AMT_LC_1<0 ,Dual('<0',1),

if(CS_AMT_LC_1>1 and CS_AMT_LC_1<=500000,Dual('1-500,000',2),

if(CS_AMT_LC_1>=500001 and CS_AMT_LC_1<=1000000,Dual('500,001-1,000,000',3),

if(CS_AMT_LC_1>=1000001 and CS_AMT_LC_1<=2000000,Dual('1,000,001-2,000,000',4),

if(CS_AMT_LC_1>=2000001 and CS_AMT_LC_1<=5000000,Dual('2,000,001-5,000,000',5),

if(CS_AMT_LC_1>=5000001 and CS_AMT_LC_1<=10000000,Dual('5,000,001-10,000,000',6),

if(CS_AMT_LC_1>10000000,Dual('Above 10,000,000',7))))))))

[krishna20]

Thank You Anbu

[danieloberbilli]

Is it possible to move this part to the script?

When doing it within the script, you could create a field with these values (e.g. F1).

1) You load the field first without connection to the data but with the identical labels and in the right order. In loading it the first time and with the same name and fieldvalues - QV will take over this sorting order for the following 'real' field. In your object on the dashboard, choose as sorting: Load Order

e.g.

SortOrder_tmp:

LOAD * INLINE [

    F1

    <0

    "1-500,000"

    "500,001-1,000,000"

   ...and so on

];

2) Load/create 'real' field:

Data:

LOAD

if(CS_AMT_LC_1<0 ,'<0',

if(CS_AMT_LC_1>1 and CS_AMT_LC_1<=500000,'1-500,000',

...and so on

as F1

FROM ...

3)  Drop table SortOrder_tmp;