Forum Discussion

ArchStanton's avatar
ArchStanton
Icon for Power Participant rankPower Participant
3 years ago
Solved

Date Bandings require sorting in visual

Hi,   I have various date bands ranging from 0-3mths, 3-6 and so on all the way up to 12 mths +. These are not sorted when I add them to the Y axis in my visual so I was thinking of ranking them 1...
Need Help
  • v-luwang-msft's avatar
    v-luwang-msft
    3 years ago

    Hi ArchStanton ，

    Pls test the below:

    RANK = 
IF (
    [Age Profile Creation Months] = "0-3 Months",
    1,
    IF (
        [Age Profile Creation Months] = "3-6 Months",
        2,
        IF (
            [Age Profile Creation Months] = "6-9 Months",
            3,
            IF (
                [Age Profile Creation Months] = "9-12 Months",
                4,
                IF ( [Age Profile Creation Months] = "12+ Months", 5,BLANK() )
            )
        )
    )
)

    Return:

     

     

    Best Regards

    Lucien

     

ArchStanton's avatar
ArchStanton
Icon for Power Participant rankPower Participant
3 years ago

 

I've tried lots of variations and the error just keeps appearing:

 

 

johnt75's avatar
johnt75
Icon for Super User rankSuper User
3 years ago

the switch statement should not have any = signs in it. the first argument should be the column name, then a list of pairs of values separated by commas, see my original post for the template.

  • ArchStanton's avatar
    ArchStanton
    Icon for Power Participant rankPower Participant
    3 years ago

    Thanks, I managed to get it to work but its only given my 5's and 6's for some reason?

     

     

    In the meantime I've copied the original Date Banding Column - modified it by substituting Date Bands for No's 1-5 and then applied a Sort on the column and this seems to have done the trick. I'd still like to know why the SWITCH code is ignoring 1,2,3,4 though?

    • ArchStanton's avatar
      ArchStanton
      Icon for Power Participant rankPower Participant
      3 years ago

       

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

      the strings would need to match exactly - same capitalization of letters, same number of spaces etc.