Forum Discussion

AllisonB's avatar
AllisonB
Frequent Visitor
1 year ago
Solved

Add an index/order column

I have created a table that I'm using to group data but I need to be able to order it correctly in visulizations and so want to add an index/order column but I can't work out where or how to add anot...
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi AllisonB 

     

    Please try this:

    Unique Callers Band Table = 
VAR _Table1 = SUMMARIZE(ADDCOLUMNS(GENERATESERIES(1,20,1),"Times Called",FORMAT([Value],"")),[Times Called])
VAR _Table2 = {"21-50"}
VAR _Table3 = {"51-100"}
VAR _Table4 = {"101-200"}
VAR _Table5 = {"201+"}
VAR _UnionTable = ADDCOLUMNS(UNION(_Table1,_Table2,_Table3,_Table4,_Table5),"_NUM",
    VAR _String = IF(CONTAINSSTRING([Times Called],"-"),"-","+")
    VAR _Num = SEARCH(_String,[Times Called],1,BLANK())
    RETURN
    VALUE(IFERROR(MID([Times Called],1,_Num-1),[Times Called])))
RETURN 
SELECTCOLUMNS(_UnionTable,[Times Called],"Sort1",RANKX(_UnionTable,[_NUM],,ASC))

    The result is as follow:

     

     

    Best Regards

    Zhengdong Xu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous's avatar
Anonymous
Not applicable
1 year ago

Hi AllisonB 

 

Please try this:

Unique Callers Band Table = 
VAR _Table1 = SUMMARIZE(ADDCOLUMNS(GENERATESERIES(1,20,1),"Times Called",FORMAT([Value],"")),[Times Called])
VAR _Table2 = {"21-50"}
VAR _Table3 = {"51-100"}
VAR _Table4 = {"101-200"}
VAR _Table5 = {"201+"}
VAR _UnionTable = ADDCOLUMNS(UNION(_Table1,_Table2,_Table3,_Table4,_Table5),"_NUM",
    VAR _String = IF(CONTAINSSTRING([Times Called],"-"),"-","+")
    VAR _Num = SEARCH(_String,[Times Called],1,BLANK())
    RETURN
    VALUE(IFERROR(MID([Times Called],1,_Num-1),[Times Called])))
RETURN 
SELECTCOLUMNS(_UnionTable,[Times Called],"Sort1",RANKX(_UnionTable,[_NUM],,ASC))

The result is as follow:

 

 

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.