4 Replies Latest reply on Aug 5, 2016 1:31 PM by Julio Moyano

sort hour minute from 6:00 to 5:59

Hi everybody,

I need to sort the hours in my worksheet. I have a row with each hour minute from 12:00 to 23:59 (1440 rows), but I need start in 06:00 and finish in 05:59. I tried to sort "manual" but it does not work.

somebody has any idea

thank you

• 1. Re: sort hour minute from 6:00 to 5:59

Create a parallel calc.

If [original time] >= 6:00 then subtract 6 hours

else

Sort on that field, but display your original time.

1 of 1 people found this helpful
• 2. Re: sort hour minute from 6:00 to 5:59

Hi,

sorry, I don't understand. I tried to do this, but the calculation contains error

thank you

• 3. Re: sort hour minute from 6:00 to 5:59

Hi Julio

Here with an explanation...

First we need the hour part... DATEPART('hour',[Datetime])

You can hopefully see we have a 2nd column now called hour.

Now to Joe's calculation - create a new calculated field

IF [hour] >= 6 THEN [hour]-6

ELSE [hour]+18

END

What that is doing... it is creating a new fake time, where 6am is really 0, 7am is 1 etc.

You will need to swap it to dimension - discrete (so it turns blue)

The trick is to put this field on the left hand side and sort in ascending order, you can then hide it (right click untick show header)

Now everything is sorted so 6am is first and 5am is last

V9.3.5 copy attached - created in Regional workbook - Weather data source (needed the time )

Cheers

Mark

• 4. Re: sort hour minute from 6:00 to 5:59

Hi Mark

I did it! it is really useful

Thank you so much,

julio