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.

parry2k's avatar
parry2k
Icon for Super User rankSuper User
1 year ago

AllisonB here it is:

 

Table = 
VAR __Table1 = SELECTCOLUMNS ( ADDCOLUMNS ( GENERATESERIES ( 1, 20, 1 ), "Times Called", FORMAT ( [Value], "" ) ), "Index", [Value], "Times Called", [Times Called] )
VAR __Table2 = ROW ( "Index", 21, "Times Called", "21-50" )
VAR __Table3 = ROW ( "Index", 51, "Times Called", "51-100" )
VAR __Table4 = ROW ( "Index", 101, "Times Called", "101-200" )
VAR __Table5 = ROW ( "Index", 201, "Times Called", "201+" )
RETURN 
UNION ( 
    __Table1,
    __Table2,
    __Table3,
    __Table4,
    __Table5
)