Filtering with calendar objects

[#1]

Hi all...

i've this type of problem,

i use two calendar object to set the start date and the end date of a period to filter the content of a table.

the filter is working correctly when in the range of date there are data to display but when for example  i select a short perdiod and in this range of time no data is included, my table show all the records instead of no record.

I'd like to know if it's possible to make the table showing no records and how.

If necessary i can post an example.

Thanks in advance for your support and your time.

Andrea

[#2]

Hi, an example would be nice.

But in general you need 1 data island. Put the date of this island in your dimension.

Because the island has al the calender dates, all dates will be shown even if there are no facts...

PS: you can also couple your island to your facts table.... if you don't get any loops

you get an island with this code: (Thanks to the other poster on this forum!)

LET vDateMin = Num(MakeDate(2010,1,1)); 

LET vDateMax = Floor(MonthEnd(Today())); 

LET vDateToday = Num(Today()); 

TempCalendar: 

LOAD

$(vDateMin) + RowNo() - 1 AS DateNumber, 

Date($(vDateMin) + RowNo() - 1) AS TempDate 

AUTOGENERATE 1 

WHILE $(vDateMin)+IterNo()-1<= $(vDateMax); 

Calendar: 

LOAD

Date(TempDate) AS Tempdate_key, 

// Standard Date Objects

Day(TempDate) AS CalendarDayOfMonth, 

WeekDay(TempDate) AS CalendarDayName, 

Week(TempDate) AS CalendarWeekOfYear, 

Month(TempDate) AS CalendarMonthName, 

'Q' & Ceil(Month(TempDate)/3) AS CalendarQuarter, 

Year(TempDate) AS CalendarYear, 

// Calendar Date Names 

WeekName(TempDate) as CalendarWeekNumberAndYear, 

MonthName(TempDate) as CalendarMonthAndYear, 

QuarterName(TempDate) as CalendarQuarterMonthsAndYear, 

// Start Dates 

DayStart(TempDate) as CalendarDayStart, 

WeekStart(TempDate) as CalendarWeekStart, 

MonthStart(TempDate) as CalendarMonthStart, 

QuarterStart(TempDate) as CalendarQuarterStart, 

YearStart(TempDate) as CalendarYearStart, 

// End Dates 

DayEnd(TempDate) as CalendarDayEnd, 

WeekEnd(TempDate) as CalendarWeekEnd, 

MonthEnd(TempDate) as CalendarMonthEnd, 

QuarterEnd(TempDate) as CalendarQuarterEnd, 

YearEnd(TempDate) as CalendarYearEnd, 

// Combo Date Examples 

'Q' & Ceil(Month(TempDate)/3) & '/' & Year(TempDate) AS CalendarQuarterAndYear, 

Year(TempDate) & '/' & 'Q' & Ceil(Month(TempDate)/3) AS CalendarYearAndQuarter, 

'Wed ' & DayStart(WeekStart(TempDate) + 3) as CalendarWednesdays 

RESIDENT TempCalendar ORDER BY TempDate ASC; 

DROP TABLE TempCalendar; 

//datums voor de reporting month selection box

Reporting_dates: load Distinct

//          CalendarMonthStart as Tempdate_key,

          CalendarMonthName as reporting_month,

          CalendarYear as reporting_year,

          //CalendarMonthStart as reporting_date,

          num(CalendarMonthStart) as reporting_date Resident Calendar;

Comparison_date: load Distinct

          num(CalendarMonthStart) as comparison_date  Resident Calendar;

[#3]

Thanks for the reply but what i need is not creating a calendar or a structure similar.

I've alreadey created 2 calendar structure, one for the start date and one for the end date.

Now i've attacched the example.

If you try to select in start date 01/01/2011 and in end date 31/05/2011 you will see that the filter will be applied and all is right.

if you try to select in start date 01/01/2011 and in end date 31/01/2011 you will see that all data will be displayed even if no data is in the range.

Wat i want from the filter (if it's possible) is that no data will be displayed in the table or a message will be shown.

Thanks

Andrea

[#4]

None can help?

[#5]

Hi, your qvw starts to reload when i open it... think you have a macro?

[#6]

Sorry......

[#7]

ok,

Here you have a slider on the field....