Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
=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))))))))
=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))))))))
Thank You Anbu
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;