Forum Discussion

Moonc's avatar
Moonc
Frequent Visitor
3 years ago

RANKX Issues with 0s

I want my rank to start at 0 and go from there. Currently it is ranking my 0 values and the next lowest value as 1 and then ranking =RANKX(FILTER(ALLSELECTED( [Program]), [NUMBER] ), [NUMBER],,ASC) ...
FreemanZ's avatar
FreemanZ
Icon for Super User rankSuper User
3 years ago

hi Moonc 

how about:

=RANKX(ALLSELECTED( [Program]), [NUMBER],,ASC)

Moonc's avatar
Moonc
Frequent Visitor
3 years ago

FreemanZ That works for when I am not working in a matrix. I should have made this more clear. I have a matrix with both program and subprogram, and I need to have a rank for each. So the formula that I am actullay using looks like the below

 

RANK = 

VAR _program = RANKX(ALLSELECTED( [Program]), [NUMBER],,ASC)

VAR _subProgram = RANKX(ALLSELECTED( [subProgram]), [NUMBER],,ASC)

 

RETURN

if(isinscope(program),_program,if(isinscope(subProgram),_subProgram))

 

This returns a proper rank for the program, but the subProgram has all the same rank

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    3 years ago

    Moonc , if both are on row and program is first and sub program is second then try like

     

    RANK =

    VAR _program = RANKX(ALLSELECTED( [Program]), [NUMBER],,ASC)

    VAR _subProgram = RANKX(ALLSELECTED( [subProgram]), [NUMBER],,ASC)

    Switch (True()

    isinscope(subProgram,_subProgram,

    isinscope(program),_program

    )

     

    The first one or the parent is always isinscope for the child 

     

    IsInScope - Switch Rank at different levels: https://youtu.be/kh0gezKICEM

    • Moonc's avatar
      Moonc
      Frequent Visitor
      3 years ago

      amitchandak This does not work, I am seeing the program ranks correct, but the subprogram ranks are incorrect. I don't see any pattern in the rank that would help me understand.