I am having a problem doing a sum for a specific date period

[#1]

Hi,

So here is my issue I need help with. Regardless of the date selected by the user - I want to display last week and the previous week sum totals.

I am using a chart object displayed as a straight table. The chart does not have any dimensions.

To sum last week date range I use the following: sum({$<Date={">=$(=weekend(max(Date),-1)))<=$(=weekend((max(Date))))"}>} sales)

To sum the previous week I am using: sum({$<Date={">=$(=weekstart(max(Date),-2))<=$(=weekend(max(Date),-1))"}>} sales).

Not only are these displaying incorrect data - they are displaying the same data.

Any help would be greatly appreciated.

Thanks,

Izzy

[Gysbert_Wassenaar]

You're subtracting days, not weeks. Try these:

sum({$<Date={">$(=weekend(max(Date)-14))<=$(=weekend(max(Date)-7))"}>}

sales)

sum({$<Date={">$(=weekend(max(Date)-7))<=$(=weekend(max(Date)))"}>} sales)

It still calculates the end of the week of the max Date. That means the last week could have only sales for one day, since the rest of the week lies beyond max Date. If you want the sales of the last 7 days and the week before that leave out the weekend() functions.

[#3]

Thanks. But I am still getting the same incorrect answer - and both values are the same (meaning the -14 and -7 don't effect anything).

[Gysbert_Wassenaar · Accepted solution]

Perhaps your Date isn't a date. The calculations don't work on strings. The expressions work fine here. See attached example.

[#5]

Your answer seems right - but it is still not working for me. I am pretty sure my date field is a date field - how can I be sure?

So I went back and looked at my formula again. I had written "Date" instead of "date" - how annoying.