Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
Hi Jonathan,
I tink its working fine, Please could u explain how its doing this
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
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
I suggest that you start another thread as this is a new topic
Jonathan
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