7 Replies Latest reply on Nov 11, 2016 11:56 AM by Chris Tighe

# Previous Hour at Midnight

Hello,

I currently need help pulling the last hour.

I have

IF DATEPART('hour',NOW())-1=[Hour] THEN [Value Net Revenue] END

Which works great for pulling the previous hour unless it's midnight. When it's midnight, this solution doesn't work any longer and everything is blank. This is due to the NOW()) - 1 actually = -1 instead of 23.

I've tried numerous things but if the current hour is midnight, I need it to look at hour (23) and pull the value net revenue for that hour. (Ensuring it is still looking at the correct Date)

I'm not able to attach a workbook.

Any help would be greatly appreciated!

Thanks,

• ###### 1. Re: Previous Hour at Midnight

Try this one.

[Date hour -1hr]

Thanks,

Shin

• ###### 2. Re: Previous Hour at Midnight

If you need "hour" element as integer,

hour([Date hour -1hr])

Shin

• ###### 3. Re: Previous Hour at Midnight

Hi Try this once :

IF (if DATEPART('hour',NOW())=0 then 24 else DATEPART('hour',NOW()) end) -1=[Hour] THEN [Value Net Revenue] END

The above highlighted logic will give value 24 for midnight so that it will take 24-1=23. If it is not midnight it will work normally.

2 of 2 people found this helpful
• ###### 4. Re: Previous Hour at Midnight

I'm pretty sure this is exactly what I'm going to need. However, I won't be able to test until midnight. Thanks Hima, and I'll let you know!

1 of 1 people found this helpful
• ###### 5. Re: Previous Hour at Midnight

I think it will work perfectly , I have tested partially by adding hours to now() function to make it mid night like dateadd('hours',3,now()).

Anyways Test it once !

1 of 1 people found this helpful
• ###### 6. Re: Previous Hour at Midnight

is it working fine ?

1 of 1 people found this helpful
• ###### 7. Re: Previous Hour at Midnight

Working just as you said it would. Sorry I haven't had a chance to reply to you earlier.

Thanks for your help on this Hima.