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.

[Chanty4u]

Try this

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

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

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

[cgT]

Hi,

Thanks for the reply. Unfortunately though, it doesn't appear to work. It has ended up just returning a value of 100% which is definitely not accurate.

[Or]

You could use Now(0) instead of Today(), but this will only work if TransactionDate is actually a timestamp rather than a date. If it's a date, you'd have no way of knowing what time of day it was added.

[cgT]

I'm afraid this doesn't work either...I'm not sure that I need the first 'time(now(0))' though, as I want the first part of the expression to include the first full day of the week.

With that factored in it still isn't pulling through correctly.

[cgT]

And I can confirm that TransactionDate does return a timestamp

[cgT]

Apologies, I made an error. PerformanceDate is in fact not a timestamp, however I do have a field called 'TransactionHour' which I figure I could factor into the set analysis, like this...

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

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

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

Of course, this isn't working, but I hope I'm on the right track? 

[Or]

Now(1) returns the time of the last reload, and would be used to compare to a timestamp. There's no reason to apply time() to it (and I believe this wouldn't change the underlying value if you did, since time() is a formatting function). This is simply to replace the use of today(), which only returns a date, with something that tells you when the app was last reloaded (including the time).

Trying to do a date and time comparison gets a little tricky - you'd need anything that is before midnight on that date, and then anything that is on the same date as now(1) but has an hour that is less than or equal to hour(now()). This isn't strictly correct, but something along the lines of:

{<TransactionDate={">=$(=WeekStart(Today(1)))<$(=Today(1))"}> + <TransactionDate={"=$(Today(1))"} , TransactionHour={"<=Hour(Now(1))"} >}

Should hopefully get you going in the right direction. Note the use of today(1) and now(1) everywhere, as if the app is reloaded daily at 10am for example, then looking at it at 9am means today(0) is actually one day ahead of the last load. I can't remember offhand if 0 or 1 is the default for today(), so I'd rather spell it out.

[cgT]

This is really helpful, thank you!

It has given me a jumping off point and I feel as though this should be right:

=(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)

 But, of course, it's giving me a crazy result of -1456.3%, which is way, way out 

[Or]

Hard to say without access to your data and such.

For the top one, you don't need the second part unless you have future TransactionDate. By definition, you'll only go up to the latest available. The same should be true for the bottom set. I'm not sure why you used Today() and Now(0) rather than using (1), but I'm assuming you have your reasons. That could lead to odd results if the last reload wasn't actually just now, since (0) means "At the time of call" and not "At last reload".

I'd suggest breaking this down into individual expressions, which should presumably help you see which part(s) are incorrect. Once you've isolated the offending sum(s), you should hopefully be able to pinpoint where the problem is.