topic Re: Last Week's Data in Expression in QlikView

Last Week's Data in Expression

Mon, 09 Feb 2015 16:24:45 GMT

How do I change this expression to show me the previous week instead of the current week? For example, today is Monday February 9th and this expression is showing me data from this week. I want it to instead show me last week (Feb 2nd week).

Count

({<[Date Changed] = {'>=$(=WeekStart( Today())) <=$(=Date(WeekEnd( Today())-2))'} >}[Type of Change]

)

Re: Last Week's Data in Expression

rubenmarin — Mon, 09 Feb 2015 16:35:06 GMT

Hi Lindsay, the easy way can be substract 7 from Today():

Count

({<[Date Changed] = {'>=$(=WeekStart( Today()-7)) <=$(=Date(WeekEnd( Today()-7)-2))'} >}[Type of Change]

)

Re: Last Week's Data in Expression

rwunderlich — Mon, 09 Feb 2015 16:47:52 GMT

I would use the InWeek() function. The function provides a shift parameter that indicates the week offset. Here's an example using different data but you'll get the idea.

// This week

=sum({<OrderDate={"=inweek(OrderDate,today(1),0)"}>}LineSalesAmount)

// Last week

=sum({<OrderDate={"=inweek(OrderDate,today(1),-1)"}>}LineSalesAmount)

-Rob

http://masterssummit.com

http://robwunderlich.com

Re: Last Week's Data in Expression

Mon, 09 Feb 2015 19:14:12 GMT

This worked, thank you!

Re: Last Week's Data in Expression

Wed, 03 Feb 2016 12:04:23 GMT

Hi Rob,

I need help on getting last week's data based on Weekstart and Weekend, as my week starting from Friday and Week ending on Thursday so I want to see the data of last week's based on week.

I tried with your expression (inweek), but it is taking from Monday to Sunday (weekstart and weekend), how to change the weekstart and weekend in script.

Pls help. thanks in advance.

Please find the attached screenshot for the same.

Re: Last Week's Data in Expression

rwunderlich — Sun, 07 Feb 2016 18:30:16 GMT

The InWeek() function takes a 3rd parm that sets the week start day. Take a look at the Help for InWeek().

-Rob

http://masterssummit.com

http://qlikviewcookbook.com