Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello all,
I have a rather large table with a date field which need sliced and diced in various ways. To this end I created a calendar table to correspond to the field. Within the calendar I set up flags to track the following date ranges:
Year to Date
Quarter to Date
Month to Date
Last Year
Last Quarter
Last Month
The code for creating the calendar is as follows:
open_date_calendar:
Load
Num AS OPEN_DATE,
week(TempDate) As Week,
Year(TempDate) As Year,
Month(TempDate) As Month,
Day(TempDate) As Day,
ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,
Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,
WeekDay(TempDate) as WeekDay,
if(InYearToDate(TempDate, today(),0), 1, 0) as CurYearFlag,
if(InQuarterToDate(TempDate, today(),0), 1, 0) as CurQtrFlag,
if(InMonthToDate(TempDate, today(),0), 1, 0) as CurMonthFlag,
if(InYear(TempDate, today(),-1), 1, 0) as LastYearFlag,
if(InQuarter(TempDate, today(),-1), 1, 0) as LastQtrFlag,
if(InMonth(TempDate, today(),-1), 1, 0) as LastMonthFlag
Resident TempCalendar
Order By TempDate ASC;
Drop Table TempCalendar;
The last six fields in the table correspond to the date ranges listed above. What I need is a listbox in the application that lists the six date ranges and filters the underlying data based on the flags. This has been quite difficult as you can imagine as there is no easy way to set up a single list box to filter six different fields.
I've kind of hacked it together using text boxes and a lot of Actions as well as some conditional coloring to make it look like a listbox, but I'm wondering if anyone has any ideas on an easier way. I'm open to restructuring the calendar table in whatever way necessary. My goal is a list box that looks like this:
So if you click "Year to Date" it would select all records where CurYearFlag = 1 or if you were to click "Quarter to Date" it would select all records where CurQtrFlag = 1 etc.
Accepted Solutions
