topic Re: Two Date Fields in a table in QlikView

Two Date Fields in a table

Mon, 13 Jan 2014 21:07:34 GMT

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

Re: Two Date Fields in a table

Mon, 13 Jan 2014 21:38:38 GMT

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.

Re: Two Date Fields in a table

Mon, 13 Jan 2014 22:42:09 GMT

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.

Re: Two Date Fields in a table

Mon, 13 Jan 2014 23:46:38 GMT

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.

Re: Two Date Fields in a table

swuehl — Mon, 13 Jan 2014 23:58:59 GMT

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)

Re: Two Date Fields in a table

hic — Tue, 14 Jan 2014 07:22:17 GMT

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

HIC

Re: Re: Two Date Fields in a table

Anonymous — Tue, 14 Jan 2014 13:23:58 GMT

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.

Re: Two Date Fields in a table

Tue, 14 Jan 2014 23:22:02 GMT

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