Two dates in same table

[#1]

How to manage two dates in a single table with single calender?

[#2]

I usually create a master calendar then reload it resident qualified.

Calendar1:

LOAD

Date(TempDate) as CalendarDate,

Month(TempDate) as Month,

Year(TempDate) as Year

...

QUALIFY *;

Calendar2:

LOAD * Resident Calendar;

RENAME FIELD Calendar2.CalendarDate to ShipDate;

UNQUALIFY *;

[datanibbler]

Hi Petrova,

do you have to build charts for either of those two dates?

=> I have one chart where I had a similar scenario: For each distinct event in the list, there were two dates:

- the day it happened

- the day it was reported

=> I had to build one chart for either of those.

=> I would have had to link the table to my master_calendar twice via the two different fieldsf

<=> That cannot be done in QlikView, you can link only once via one or several field(s).

=> so I ended up generating a (smaller) alternative calendar for that chart.

HTH

Best regards,

DataNibbler

[Clever_Anjos]

It´s a tricky question.

What kind of selections you want to make into your application?

[brijesh1991]

Load *, Date1 as Date, 'Date1' as DateFlag Resident Table1;

Concate

Load *, Date2 as Date, 'Date2' as DateFlag Resident Table1;

Now link this concatnated table with your Calender Date.

And in front end, when ever you want data for Date1, then use setanalysis: DateFlag={'Date1'} and vice versa for Date2.

[hic]

I think you should load the Calendar several times, once per date. See http://community.qlik.com/blogs/qlikviewdesignblog/2012/08/30/master-table-with-multiple-roles

But if you want to have one, generic date, you should do it the way Brijesh Gadhesariya suggests.

HIC

[#7]

Henric

If you use multiple calendars it makes it more difficult to use the same date dimension in one bar and use one selection to affect multiple dates.  For example:

I want to count the # of orders shipped and the # of orders placed in one bar for each week.

I use a linked table to do this, then set analysis to determine which date.  I have not found a better way.  I do this with many of my dashboards to allow the user to use one list box for each date dimension and to allow multiple expressions for one date dimension in charts.  If you know of a better way, please share.  We have one client who has 9 dates per record to follow the process of an application from receipt to funded.  It does create a long link table but only 3 columns wide and filtered to only those records that contain dates for that particular date field.  See below.

Load the sales table then load the link table:

Note:  %Key is the unique identifier in the sales table.

LInk:

Load %Key

ShippedDate as %DateKey

“Shipped” as DateType

resident sales

where len(Shipped Date) > 1;

Load %Key

OrderedDate as %DateKey

“Ordered” as DateType

resident sales

where len(OrderedDate) > 1;

Load ONE master calendar with

%DateKey as unique key

and all date dimensions

Expressions in chart would be:

sum({<DateType = {'Shipped'}>} Amount)

sum({<DateType = {'Ordered'}> Amount)

dimension for bar chart can be WeekEnding, Month, etc...  YOu are now seeing both the $ amount shipped and the $ amount ordered in one bar chart, stacked or grouped.  Works for me.

Debbie Pyykkonen

Archipelago IS, LLC | Director, Business Analytics| www.archipelagois.com

303 S. Broadway, Suite 200-169 | Denver, Colorado  80209

Twitter | LinkedIn | Facebook |

(: 303-304-6425 |  *: dpyykkonen@archipelagois.com

Check out our  Workshops and Events!

Download QlikView for FREE!

[#8]

Hi Petrova,

Try to use set analysis in each expression. This way you can handle several dates in the same table diregarding the selection of the date in the calendar filter.

For example:

Sum({<Year={2013}>} Sales )

Sum({<Year={2014}>} Sales )

Regards

[hic]

You're right that with multiple calendars it is more difficult to use the same date dimension in the chart.

And for that purpose you can create a generic date your way (which basically is the same as Brijesh's solution). However, if you do this, then one specific order dollar will be linked to two dates, and you need to use Set analysis to define your expressions, e.g.

     Sum({$<[Date Type]='Shipped'>} Amount)

But with a generic date you cannot make selections like "Ordered in Jan, and Shipped in Feb". For this you need multiple calendars.

Luckily, you can use both ways simultaneously in the same app.

HIC

[#10]

Jaime, your solution doesn't take into account the different date fields.