Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All
I have a list box showing Weekly dates(from-to), week number in the month and month of the week.
I have written a sort expression to sort by week number "mid(Weeks,12,1) ". I would now like to have a second condition in the sort expression to will sort first by week number, then months in a cronological order. How can I do this?
I would also like my selected dates to show at the top of the list box as they currently at the bottom which I need to scroll down to see.
You could also generate a dummy table in the order you want and then use Load Order in the sort.
WeekSort:
LOAD
Num(Day(RangeMax(weekstart(RE_DAY),monthstart(RE_DAY))),'00')
&'-'&
Num(Day(RangeMin(weekend(RE_DAY),monthend(RE_DAY))),'00')
&' Week '&
If(( week(WeekStart(RE_DAY)) - week(MonthStart(RE_DAY))+1) < 0,( week(WeekStart(RE_DAY)) - week(MonthStart(RE_DAY))+1)+52, ( week(WeekStart(RE_DAY)) - week(MonthStart(RE_DAY))+1)) &' Of '& Month(RE_DAY) as Weeks
;
LOAD MakeDate(2012)+RecNo() as RE_DAY AutoGenerate 2000;
// Do the rest of the script...
DROP TABLE WeekSort; // Drop dummy table at script end
-Rob
Hi,
If you have a date field and then use the Dual() for setting the Sorting.
LOAD
*,
DUal(Weeks, WeekEnd(Date)) AS Weeks
FROM DataSource;
Now in sort tab select Numeric. Hope this helps you.
Regards,
Jagan.
Hi Jagan
Thanks for your reply
Please my script for Weeks:
Num(Day(RangeMax(weekstart(RE_DAY),monthstart(RE_DAY))),'00')
&'-'&
Num(Day(RangeMin(weekend(RE_DAY),monthend(RE_DAY))),'00')
&' Week '&
If(( week(WeekStart(RE_DAY)) - week(MonthStart(RE_DAY))+1) < 0,( week(WeekStart(RE_DAY)) - week(MonthStart(RE_DAY))+1)+52, ( week(WeekStart(RE_DAY)) - week(MonthStart(RE_DAY))+1)) &' Of '& Month(RE_DAY) as Weeks;
How would I apply the Dual fuction here?
When I use Week and Month fields I always use the following format:
201501 ... 201552 for weeks
and
201501 ... 201512 for months,
these formats ghive you natural (numeric) sorting
Starting from a date do as follows:
for Weeks: WeekYear(Date)*100 + Week(Date);
for Months: Year(Date)*100 + Month(Date);
Hope this helps
Hi Jagan
I think you solution could help me but I'm just strucggling to apply it to my code as it is a bit complex. Can you please assist with that. Please see the script in my previous comment
Hi,
Try this
LOAD
*,
Dual(
Num(Day(RangeMax(weekstart(RE_DAY),monthstart(RE_DAY))),'00')
&'-'&
Num(Day(RangeMin(weekend(RE_DAY),monthend(RE_DAY))),'00')
&' Week '&
If(( week(WeekStart(RE_DAY)) - week(MonthStart(RE_DAY))+1) < 0,( week(WeekStart(RE_DAY)) -week(MonthStart(RE_DAY))+1)+52, ( week(WeekStart(RE_DAY)) - week(MonthStart(RE_DAY))+1)) &' Of '& Month(RE_DAY), WeekStart(RE_DAY)) AS Week
Regards,
Jagan.
Hi Jagan
Thank you very much for your reply...just one more question.
I'm pulling the data from a SQL table
Can I still perform a Load *, with a preceding load?
Hi,
Use preceding load like below
LOAD
*,
Dual(
Num(Day(RangeMax(weekstart(RE_DAY),monthstart(RE_DAY))),'00')
&'-'&
Num(Day(RangeMin(weekend(RE_DAY),monthend(RE_DAY))),'00')
&' Week '&
If(( week(WeekStart(RE_DAY)) - week(MonthStart(RE_DAY))+1) < 0,( week(WeekStart(RE_DAY)) -week(MonthStart(RE_DAY))+1)+52, ( week(WeekStart(RE_DAY)) - week(MonthStart(RE_DAY))+1)) &' Of '& Month(RE_DAY), WeekStart(RE_DAY)) AS Week;
SELECT
*
FROM TableName;
Regards,
Jagan.
Hi Jagan
Thanks for the solution. It works perfectly...except now the Dual function is messing up my dates.
I now have a date of 2012/01/01 showing in the list box as "26-31 Week 5 Of Dec". It is suppose to be "01-01 Week 1 Of Jan"
Before adding Dual
After adding Dual
How can I fix this?
You could also generate a dummy table in the order you want and then use Load Order in the sort.
WeekSort:
LOAD
Num(Day(RangeMax(weekstart(RE_DAY),monthstart(RE_DAY))),'00')
&'-'&
Num(Day(RangeMin(weekend(RE_DAY),monthend(RE_DAY))),'00')
&' Week '&
If(( week(WeekStart(RE_DAY)) - week(MonthStart(RE_DAY))+1) < 0,( week(WeekStart(RE_DAY)) - week(MonthStart(RE_DAY))+1)+52, ( week(WeekStart(RE_DAY)) - week(MonthStart(RE_DAY))+1)) &' Of '& Month(RE_DAY) as Weeks
;
LOAD MakeDate(2012)+RecNo() as RE_DAY AutoGenerate 2000;
// Do the rest of the script...
DROP TABLE WeekSort; // Drop dummy table at script end
-Rob