How to Compare Date field with 3 other date fields

[#1]

Hi,

I have 2 table.

MasterCalender:

Year,Quater,Month,Week (I have 2011,201,2013 year in the table)

Exceptions:

ID, Priority, Status, Date_Raised, Date_Pickedup, Date_Closed.

I want to create 4 filter for Year, Quater, Month and Week. I am using master calender table for these values.

Based on the user selection i have to check in three date fields raised,pickedup and closed in my exception table.

Questions i have.

1.) Do i need to join MasterCalender table to Exception table. If yes how to join and what columns i use.

2.) How to filter the records based on selection.

I appreciate if someone reply me.

Thanks,

Chris

[marwen_garwachi]

hello ,

1. If you have loaded your two tables correctly the joint should be done automatically. You can check by going to File -> Table Viewer (Ctrl + T).Tables must be connected by a line.If not, click on auto-layout .
2. you have just to insert 4 list box, one for each field of MasterCalender  (right mouse click -> new sheet object -> list box ), then you have juste to select dates .

Regards,

Marwen

[#3]

Hi Sekhar,

Please see Henric's blog: http://community.qlik.com/blogs/qlikviewdesignblog/2012/10/16/the-master-calendar

There are three ways to create the master calendar. And then as Marwen Garwachi has mentioned you must make sure to join the two tables.

Normally I prefer Henric's third choice. By using the first and last date in your data, you will make sure additional feature data (dates) are not missed out in you calendar.

[preminqlik]

HI THERE,

IF YOU MAP THE TABLES THEN AUTOMATICALLY EVERY DATE IS SYNCHRONISED WITH CORRESPONDING TRANSACTION DATES...SO  NO NEED TO CONFUSE AGAIN WITH THREE DATES.....JUST MAP WITH Date_Raised TO MASTER CALENDAR..

prem

[Clever_Anjos]

If you decide do not link the tables you can use Set Analysis to make your calculations.

1) Please add a field "date" to your calendar (if you already didn´t do that)

2) Now you can use Set Analysis to restrict the rows from your Expression, using something like : Count({<Date_Raised=P(DateCalendarField)>} ExceptionID)