Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Two Date Fields in a table

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

1 Solution

Accepted Solutions
mov
Not applicable

Re: Re: Two Date Fields in a table

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.

7 Replies
Not applicable

Re: Two Date Fields in a table

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.

Not applicable

Re: Two Date Fields in a table

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. 

Not applicable

Re: Two Date Fields in a table

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
Not applicable

Re: Two Date Fields in a table

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)

Henric_Cronström
Not applicable

Re: Two Date Fields in a table

mov
Not applicable

Re: Re: Two Date Fields in a table

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.

Not applicable

Re: Two Date Fields in a table

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