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 another column into the formula used to create the table. 

What I want is a column with 1 - 24 following the order they can been seen in in the table below

 

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.

2 Replies

  • parry2k's avatar
    parry2k
    Super 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
)

     

     

     

     

  • 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.