topic Re: Static Week Numbers with dynamic start date in QlikView

Static Week Numbers with dynamic start date

pauledrich — Thu, 04 Jun 2015 12:22:29 GMT

I have been grappling with this problem for some time and have made some headway but always hit the same issue.

we have an number of locations where a project has an implementation date and would like to compare each location with the implementation being week 0 as shown below:

Location	Implementation Date	Week 0	Week 1	Week 2
A	22/05/2015	Start Week Sales	Sum(Sales) -1 week after 22/05/2015	Sum(Sales) -2 week after 22/05/2015
B	20/04/2015	Start Week Sales	Sum(Sales) -1 week after 20/04/2015	Sum(Sales) -2 week after 20/04/2015
C	09/03/2015	Start Week Sales	Sum(Sales) -1 week after 09/03/2015	Sum(Sales) -2 week after 09/03/2015
D	09/02/2015	Start Week Sales	Sum(Sales) -1 week after 09/02/2015	Sum(Sales) -2 week after 09/02/2015
E	21/01/2015	Start Week Sales	Sum(Sales) -1 week after 21/01/2015	Sum(Sales) -2 week after 21/01/2015

Any steer or help to implement a script solution would be appreciated - thanks in advance.

Re: Static Week Numbers with dynamic start date

robert_mika — Thu, 04 Jun 2015 12:31:58 GMT

Try this for first week:

sum({$<[Implemetaion Day]={">=[Implemetaion Day]+1<=[Implemetaion Day]+6"}>Sales)

and so one for next weeks...

Re: Static Week Numbers with dynamic start date

pauledrich — Thu, 04 Jun 2015 20:31:41 GMT

Thank you Robert,

Scripting would help with calculating to today with a dedicated dimension without the need to have potentially an unknown number of expressions?

Really appreciate any additional advice.