Forum Discussion

am_i_really's avatar
am_i_really
Frequent Visitor
3 years ago
Solved

Showing Time range in order

Hi All, I'm having an issue with getting my data to sort correctly. I have the following switch statement I used to group the data into time ranges but it does not show in correct order. It wont allow me to sort the column either

 

 

TimeGroup = SWITCH(
    TRUE(),
    AND(SF_YTD_Master[ORDER_TIME] >= TIME(00,00,00), SF_YTD_Master[ORDER_TIME] < TIME(03,00,00)), "12-3 am",
    AND(SF_YTD_Master[ORDER_TIME] >= TIME(03,00,00), SF_YTD_Master[ORDER_TIME] < TIME(06,00,00)), "3-6 am",
    AND(SF_YTD_Master[ORDER_TIME] >= TIME(06,00,00), SF_YTD_Master[ORDER_TIME] < TIME(09,00,00)), "6-9 am",
    AND(SF_YTD_Master[ORDER_TIME] >= TIME(09,00,00), SF_YTD_Master[ORDER_TIME] < TIME(12,00,00)), "9-12 pm",
    AND(SF_YTD_Master[ORDER_TIME] >= TIME(12,00,00), SF_YTD_Master[ORDER_TIME] < TIME(15,00,00)), "12-3 pm",
    AND(SF_YTD_Master[ORDER_TIME] >= TIME(15,00,00), SF_YTD_Master[ORDER_TIME] < TIME(18,00,00)), "3-6 pm",
    AND(SF_YTD_Master[ORDER_TIME] >= TIME(18,00,00), SF_YTD_Master[ORDER_TIME] < TIME(21,00,00)), "6-9 pm",
    AND(SF_YTD_Master[ORDER_TIME] >= TIME(21,00,00), SF_YTD_Master[ORDER_TIME] <= TIME(23,59,59)), "9-12 am"
)

 

Thanks in advance!

Need Help
Tips and Tricks
  • Arul's avatar
    Arul
    3 years ago

    am_i_really ,

    You have sort this column at model level. Try the below code to create a calculated column and then use sort by column option at model level,

    TimeGroup Sort = SWITCH(
    TRUE(),
    AND(SF_YTD_Master[ORDER_TIME] >= TIME(00,00,00), SF_YTD_Master[ORDER_TIME] < TIME(03,00,00)), 1,
    AND(SF_YTD_Master[ORDER_TIME] >= TIME(03,00,00), SF_YTD_Master[ORDER_TIME] < TIME(06,00,00)), 2,
    AND(SF_YTD_Master[ORDER_TIME] >= TIME(06,00,00), SF_YTD_Master[ORDER_TIME] < TIME(09,00,00)), 3,
    AND(SF_YTD_Master[ORDER_TIME] >= TIME(09,00,00), SF_YTD_Master[ORDER_TIME] < TIME(12,00,00)), 4,
    AND(SF_YTD_Master[ORDER_TIME] >= TIME(12,00,00), SF_YTD_Master[ORDER_TIME] < TIME(15,00,00)), 5,
    AND(SF_YTD_Master[ORDER_TIME] >= TIME(15,00,00), SF_YTD_Master[ORDER_TIME] < TIME(18,00,00)), 6,
    AND(SF_YTD_Master[ORDER_TIME] >= TIME(18,00,00), SF_YTD_Master[ORDER_TIME] < TIME(21,00,00)), 7,
    AND(SF_YTD_Master[ORDER_TIME] >= TIME(21,00,00), SF_YTD_Master[ORDER_TIME] <= TIME(23,59,59)), 8
)

    Then select the time group column and then use time group sort in sort by column option,

    Thanks,

    Arul

2 Replies

  • Arul's avatar
    Arul
    Super User
    3 years ago

    am_i_really ,

    You have sort this column at model level. Try the below code to create a calculated column and then use sort by column option at model level,

    TimeGroup Sort = SWITCH(
    TRUE(),
    AND(SF_YTD_Master[ORDER_TIME] >= TIME(00,00,00), SF_YTD_Master[ORDER_TIME] < TIME(03,00,00)), 1,
    AND(SF_YTD_Master[ORDER_TIME] >= TIME(03,00,00), SF_YTD_Master[ORDER_TIME] < TIME(06,00,00)), 2,
    AND(SF_YTD_Master[ORDER_TIME] >= TIME(06,00,00), SF_YTD_Master[ORDER_TIME] < TIME(09,00,00)), 3,
    AND(SF_YTD_Master[ORDER_TIME] >= TIME(09,00,00), SF_YTD_Master[ORDER_TIME] < TIME(12,00,00)), 4,
    AND(SF_YTD_Master[ORDER_TIME] >= TIME(12,00,00), SF_YTD_Master[ORDER_TIME] < TIME(15,00,00)), 5,
    AND(SF_YTD_Master[ORDER_TIME] >= TIME(15,00,00), SF_YTD_Master[ORDER_TIME] < TIME(18,00,00)), 6,
    AND(SF_YTD_Master[ORDER_TIME] >= TIME(18,00,00), SF_YTD_Master[ORDER_TIME] < TIME(21,00,00)), 7,
    AND(SF_YTD_Master[ORDER_TIME] >= TIME(21,00,00), SF_YTD_Master[ORDER_TIME] <= TIME(23,59,59)), 8
)

    Then select the time group column and then use time group sort in sort by column option,

    Thanks,

    Arul