topic Re: Deriving Week from Sysdate in QlikView

Deriving Week from Sysdate

Mon, 30 Sep 2013 15:07:57 GMT

How can we derive last 5 weeks from sysdate . The week should be from Saturday to Friday . Today is monday means we should have last Saturday(22 nd Sep - 29 sEP), similarly i need to derive last 5 weeks.

Thanks,

Abhi

Re: Deriving Week from Sysdate

Mon, 30 Sep 2013 23:16:58 GMT

Can somebody help me on this pls?

Re: Deriving Week from Sysdate

swuehl — Mon, 30 Sep 2013 23:27:06 GMT

Last saturday was Sep 28th, not 22nd or 29th. Or do you want a week from Sunday to Saturday instead?

Well, if you want a week Saturday to Friday, you can use weekstart and weekend functions with an weekstart offset:

This week:

weekstart(today(),0,-2) to daystart(weekend(today(),0,-2))

Previous weeks:

weekstart(today(),-1,-2) to daystart(weekend(today(),-1,-2))

weekstart(today(),-2,-2) to daystart(weekend(today(),-2,-2))

weekstart(today(),-3,-2) to daystart(weekend(today(),-3,-2))

weekstart(today(),-4,-2) to daystart(weekend(today(),-4,-2))

Re: Deriving Week from Sysdate

Tue, 01 Oct 2013 00:44:43 GMT

This is static right. I want this to be dynamic, so that it always brings last week frpm Sunday - Saturday. If it runs today it should bring from 22nd - 28th until this week is finished. The output will be same until we have the complete week.

Re: Deriving Week from Sysdate

tresB — Tue, 01 Oct 2013 04:41:00 GMT

It's not static. It can be implemented dynamically as well. Please share a sample app with explained expected output; you might be helped in an efficient way. You might want to have a look at this qlikview design post by Henric Cronström for better understanding and learning.

Re: Deriving Week from Sysdate

swuehl — Tue, 01 Oct 2013 09:24:42 GMT

No, it's not static, it uses today() with relative week offsets (0, -1, -2, -3, -4).Last week is indicated by -1 and so on:

weekstart(today(),-1,-2)

daystart(weekend(today(),-1,-2))