topic Filtering with calendar objects in QlikView

Filtering with calendar objects

Mon, 19 Sep 2011 10:10:44 GMT

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

Re: Filtering with calendar objects

Mon, 19 Sep 2011 11:14:20 GMT

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;

Re: Filtering with calendar objects

Tue, 20 Sep 2011 10:19:48 GMT

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

Filtering with calendar objects

Thu, 22 Sep 2011 13:19:26 GMT

None can help?

Filtering with calendar objects

Thu, 22 Sep 2011 14:37:51 GMT

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

Re: Filtering with calendar objects

Thu, 22 Sep 2011 15:31:49 GMT

Sorry......

Re: Filtering with calendar objects

Thu, 22 Sep 2011 15:54:17 GMT

ok,

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