topic Re: I am having a problem doing a sum for a specific date period in QlikView

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

Mon, 03 Dec 2012 13:16:32 GMT

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

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

Gysbert_Wassenaar — Mon, 03 Dec 2012 13:40:18 GMT

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.

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

Mon, 03 Dec 2012 13:54:46 GMT

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).

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

Gysbert_Wassenaar — Mon, 03 Dec 2012 15:09:21 GMT

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

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

Mon, 03 Dec 2012 15:39:39 GMT

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.