Master Calender Scenario

[#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

[#2]

If your Exception table has date fields  or is related to a table that has just have to add the key created in MasterCalender

[#3]

These fields in exception table Date_Raised, Date_Pickedup, Date_Closed are date fields

[#4]

I can spend the file to see what I can do

[jvitantonio]

Hello Sekhar,

That will depend on your requirements.

The question that you need to ask here is: "What would I like to analyze?" and "What's my driver?"

If you are trying to analyze data based on your Date_Raised, then you should link your calendar to this field, and use the other Date fields to support your analysis.

Your requirements will be able to tell you this. What are users expected to see when they select a date?

Another option would be to leave the calendar as a island table (i.e, not connected to your main schema). You can then play with your Date fields like this:

=Sum({$<Date_Raised = P(CalendarDate) >} Sales)

This is an example on how you can use a island table. This will give you the Sales where Date_Raised equals any of the values selected in your master calendar date field (CalendarDate).

In your case, you don't have any values in your table, but only ID's, Priority and Status, so I guess you can have a straight table and in your dimension you could do something like:

Dim1 = Priority

Dim3 = If(Date_Raised = CalendarDate, Date_Raised)

Expressions = only(Status)

This will show you the Priority and Status when Date_Raised  equals whatever the user selected in CalendarDate.

I hope this helps.

JV

BI Experience | A place to share our Business Intelligence experiences

[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  (master key is [Quater])
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 just to click on dates, the records will be instantly filtred .

Marwen

[#7]

Hi Sekhar,

Please see attached example. I have created a master calendar with min max values of three different date field.

And the QVW contains two scenarios of how you can use the calendar:

Sheet 1: This is an example of how you can link one out of three Date fields to the calendar. Please not that if you select dates in the calendar lists, you will only link to the field Date1.

Sheet 2: This is how you could use a Calendar table island. It is not link to Table1. Instead I am using an if statement to count the Item field for each year.

Hope this will help you understand the possible implications better and help you choose the correct method in your solution.

Cheers