Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

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

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

Tags (4)
1 Solution

Accepted Solutions
Highlighted
MVP & Luminary
MVP & Luminary

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

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


talk is cheap, supply exceeds demand

View solution in original post

4 Replies
Highlighted
MVP & Luminary
MVP & Luminary

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

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.


talk is cheap, supply exceeds demand
Highlighted
Not applicable

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

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

Highlighted
MVP & Luminary
MVP & Luminary

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

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


talk is cheap, supply exceeds demand

View solution in original post

Highlighted
Not applicable

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

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.