How to create range of weks and month in same listbox for below table.

[#1]

Date	A	B
01-Jan-2012	2	10
15-Jan-2012	4	15
30-Jan-12	6	1
01-Oct-2011	7	70
01-Dec-2011	5	10

Hi,

For above table, I want to to create list box which will show below ranges for selection

Current week,

Last 2 weeks,

Last 4 weeks,

Current Month,

Last 8 weeks,

Last 52 weeks,

Current Year

Any suggestion to create such listbox will be really helpful to me.

[Anonymous]

This is a situation where easier to create an example than to explain... 🙂

See attached.  I'm not sure I got the definition of the "Last N Weeks" right - used full weeks before the start of the current week - but it doesn't matter, you can adjust the rules any way you want.  And, I hope you can now create the "missing" ranges.

Regards,

Michael

[jonathandienst · Accepted solution]

Hi

Here is another approach. The inline load creates the ranges based on today's date and then joins the Range field to the calendar date field (CalDate in this script):

Let zToday = Today(1);
Ranges:
LOAD Range, Date(Evaluate(RangeStart)) As RangeStart, Date(Evaluate(RangeEnd)) As RangeEnd;
LOAD * INLINE [
Range;                     RangeStart;                     RangeEnd
Today;                     zToday;                          DayEnd(zToday)
Yesterday;                zToday-1;                          DayEnd(zToday-1)
This Week;                WeekStart(zToday);           WeekEnd(zToday)
Last Week;                WeekStart(zToday,-1);           WeekEnd(zToday, -1)
Last 2 Weeks;         WeekStart(today(1),-1);      WeekEnd(zToday)
This Month;               MonthStart(zToday);           MonthEnd(zToday)
Last Month;                   MonthStart(zToday,-1);           MonthEnd(zToday,-1)
Last Two Months;      MonthStart(zToday,-1);       MonthEnd(zToday)
This Year;                YearStart(zToday);                YearEnd(zToday)
Last Year;                YearStart(zToday,-1);           YearEnd(zToday,-1)
YTD;                         YearStart(zToday);               DayEnd(zToday-1)
Last YTD;               YearStart(zToday,-1);          AddYears(DayEnd(zToday-1), -1)
MTD;                         MonthStart(zToday);               DayEnd(zToday - 1)
Last MTD;               MonthStart(zToday, -1);          AddMonths(DayEnd(zToday-1), -1)
] (delimiter is ';');
JOIN (Ranges) IntervalMatch (CalDate) LOAD RangeStart, RangeEnd RESIDENT Ranges;
DROP FIELDS RangeStart, RangeEnd;

Add whatever other ranges that you might need. Now put the Range field in a list box...

Regards

Jonathan

Sorry about the formatting - the tabs are being scrambled when I paste...

[#4]

Hi Michael, Jonathan,

Thanks for providing the solutions. I have used Jonathen's solution successfully.

I don't know why Michael's solution didn't work for me though it created ranges. Ofcourse that is my task to find out and correct.

Thanks again for the help.