Prior week- Date problem

[Anonymous]

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

[jonathandienst · Accepted solution]

Hi

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

[Anonymous]

Hi Jonathan,

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

[Anonymous]

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

[jonathandienst]

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

[jonathandienst]

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

Jonathan

[Anonymous]

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,

S