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

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?

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.