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

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