Two Date Fields in a table

[#1]

I am new to Master Calendar and need some help understanding it.  I have two dates in my table:

CreatedDate - which is the date the item was created

ChangeDate - which is the date the item is updated

I want to have two charts:

1.  One that shows me the total number of items created by month and year

2.  One that show me the total number of items changed by month and year

I would like to have two list boxes:

One that has the months and one that has the years.  Based on those selections I want the charts mentioned above to reflex those results.  Is this something a Master Calendar can accomplish and can someone give me so pointers.

David

[#2]

What are the other Date dimensions  you need ? If you need other Date dimensions for both dates, you need to create the 2 calender tables.

[#3]

The three date dimension I will need is for any of the two dates list I will select

year

month

year and month

once they are selected the charts will reflex the results.  So it sounds like two calendar tables is the way to go. 

[#4]

In that case, Better go for 2 calender tables one for Created Date and another for Changed Date or you can directly add these fields in the same table because its only 4 new fields.

Month(Created_Date) AS CreatedYear,

Year(Created_Date) AS CreatedMonth ,

For the Month Year use , calculated Dimension like "CreatedMonth & CreatedYear" in the chart dimension.

Create the same thing for Changed_Date as well.

[swuehl]

You can consider creating a link table between your fact table (assuming having a FactID, CreatedDate, ChangedDate in your table) and a master calendar like

CrossTable (Type, Date) LOAD FactID, CreatedDate, ChangedDate Resident FactTable;

This will transform the table to a table with FactID, Type and Date fields, Date field linking to your master calendar.

Then filter your records using Type in the charts, e.g. to filter ChangedDate chart, dimension from Master calendar and as expression:

=sum({<Type = {ChangedDate}>} Amount)

[hic]

See http://community.qlik.com/message/448877#448877

HIC

[Anonymous · Accepted solution]

Using master calendar as data island will do the job.  The charts dimension is from the master calendar, and expressions compare created or changed date against the dimension.  See attached.

It is simple and reliable, but maybe not the best from the performance point if the data set is large.

[#8]

thank you Michael.  That helps me understand it better. I now just need to add additional years and I am set. I will play around with it.

David