topic Re: Prior week- Date problem in QlikView

Prior week- Date problem

Anonymous — Mon, 28 Jan 2013 06:34:33 GMT

Hi Geniuses,

I have a problem related with Prior week

Last Refresh Date	Weekday
12/10/2012	Monday
12/14/2012	Friday
12/15/2012	Saturday
12/20/2012	Thursday
1/11/2013	Friday
1/12/2013	Saturday
1/14/2013	Monday
1/18/2013	Friday
1/19/2013	Saturday
1/20/2013	Sunday

Lets say I am on sunday 1/20/2013 so I need to go to the last monday which is said to be mine prior week, it's easy for me to do so what I am doing is

=if(weekday($(vRefreshDate))='Sun',date($(vRefreshDate)-6,'MM/DD/YYYY')) where vRefreshDate is the latest refresh date and storing alll this in a variable and then use it

But here comes the tricy part lets say my last refresh date is 1/12/2013 so ideally prior week should be 1/7/2013 which is monday but that date is not available so now what I want is that if that date is not available then my variable will pick the last refresh date before that monday which is 12/20/2012

Please lemme konw if u need any clarifications

Thanks in advance

Shashank

Re: Prior week- Date problem

jonathandienst — Mon, 28 Jan 2013 06:54:10 GMT

Something like this:

=if(weekday(vRefreshDate)='Sun',max({<[Last Refresh Date] = {"<=$(=date(vRefreshDate-6,'MM/DD/YYYY'))"}>} [Last Refresh Date]), vRefreshDate)

Assuming [Last Refresh Date] is the name of the field and that it is in MM/DD/YYYY format.

Regards

Jonathan

Re: Prior week- Date problem

Anonymous — Mon, 28 Jan 2013 07:41:53 GMT

Hi Jonathan,

I tink its working fine, Please could u explain how its doing this

Re: Prior week- Date problem

Anonymous — Tue, 29 Jan 2013 08:04:51 GMT

Hi Jonathan,

Could you please tell me how can I implement this set in the script and store its value into the variable

Thanks in advance

Re: Prior week- Date problem

jonathandienst — Tue, 29 Jan 2013 09:09:33 GMT

In psuedo-code:

If the weekday is a Sunday,

Take the most recent (Max) value of Last Refresh Date that is before or on (<=) 6 days before vRefreshDate (vRefreshDate-6) and return that date.

else

return vRefreshDate.

Regards

Jonathan

Re: Prior week- Date problem

jonathandienst — Tue, 29 Jan 2013 09:10:19 GMT

I suggest that you start another thread as this is a new topic

Jonathan

Re: Prior week- Date problem

Anonymous — Tue, 29 Jan 2013 09:53:46 GMT

Hi J,

I have already started a new thread

http://community.qlik.com/message/305022#305022

Thot that may be you know this

if You know it please reply in new thread

Thanks,