BusinessObjects Board

Change date based on time?

building reports webintelligence
#1

Hello gurus!

I’ve a conundrum that I am struggling with, and it may be simple or really complex.

I have a sales date and time, and want to change the physical date based on the time of the sale.
This is due to sales promotions not running midnight to midnight, but until 5am the following day.

As an example, if a sale is made between 00:00:01 and 04:59:59 on 25/01/2023, I want it to show the date in a variable of 24/01/2023 or if it it outside of this time range to stay as 25/01/2023, and is usable as a date. The plan is to then make a custom dimension, rather than a variable to use going forward, once the results have been validated.

Any guidance is appreciated :slight_smile:

#2

If this is a company standard, push this as far back up the stream as possible.

By that, I mean don’t repeat a complex solution in Webi, try and push a more elegant solution back to the universe or ideally the database itself.

If that’s not possible, then you’ll need a messy If statement in Webi that looks at the hour and leaves the data or adds/subtracts 1 day as required.

3 Likes
#3

I could be overlooking something, but this seems like you could simply subtract 5 hours from the Sales Date and Time…

=RelativeDate([Sales Date and Time]; -5; HourPeriod)

3 Likes
#4

This would be ideal, and something to plan for the future. The initial of getting it out there and running it for a period to prove concept it the hardest part.

1 Like
#5

Using @nscheaffer 's solution will work for now then, until you can put something more robust/global in place. We have a similar set up where calendar day and working day are offset by x hours due to shift patterns. This is all calculated in the back end so that any transaction has both a calendar day and working day that it is associated with.

1 Like