Compare Sales Values Based on Load Times

[cgT]

Hi there,

I'm currently tying to compare sales values, week on week, based on the data in the current reload. Here is my expression:

=(sum({<TransactionDate={">=$(=WeekStart(Today()))<=$(=date(Today()))"}>}SoldValue)-sum({<TransactionDate={">=$(=WeekStart(Today()-7))<=$(=date(Today()-7))"}>}SoldValue))
/
sum({<TransactionDate={">=$(=WeekStart(Today()))<=$(=date(Today()))"}>}SoldValue)

I am aware that this expression returns values for whole days' of sales. This means that at that beginning of, say, Tuesday, the total calculation factors in the total sales for the Tuesday the week prior which is problematic as the current Tuesday hasn't had a complete day of sales yet.

What I'm trying to achieve is that this up/down expression includes the sales up to the equivalent timestamp (or reload time, whichever) for the Tuesday the week before. So, if it's currently 10am on Tuesday 30th May, then ideally I'd want the formula to use sales up to an including 10am on Tuesday 23rd May to compare against.

I hope this makes sense, and thanks for your help in advance.

[cgT · Accepted solution]

Thank you so much for your help. I've had a look at it this morning, with a fresh pair of eyes, and have gotten it to work:

=(sum({<TransactionDate={">=$(=WeekStart(Today()))<=$(=date(Today()))"}>} SoldValue)

- (sum({<TransactionDate={">=$(=WeekStart(Today()-7))<=$(=date(Today()-8))"}>} SoldValue) + sum({<TransactionDate={"$(=date(Today()-7))"} , TransactionHour={"<=$(=Hour(Now(0)))"} >} SoldValue)))

/(sum({<TransactionDate={">=$(=WeekStart(Today()))<=$(=date(Today()))"}>} SoldValue))

 This works perfectly!

Thanks again