topic Re: Two dates in same table in QlikView

Two dates in same table

Tue, 07 Jan 2014 16:12:34 GMT

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

Re: Two dates in same table

Tue, 07 Jan 2014 16:21:15 GMT

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 *;

Re: Two dates in same table

datanibbler — Tue, 07 Jan 2014 16:28:33 GMT

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

Re: Two dates in same table

Clever_Anjos — Tue, 07 Jan 2014 16:56:00 GMT

It´s a tricky question.

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

Re: Two dates in same table

brijesh1991 — Thu, 09 Jan 2014 09:07:44 GMT

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.

Re: Two dates in same table

hic — Thu, 09 Jan 2014 11:26:57 GMT

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

Re: Two dates in same table

Thu, 09 Jan 2014 15:05:49 GMT

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!

Re: Two dates in same table

Thu, 09 Jan 2014 15:23:39 GMT

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

Re: Two dates in same table

hic — Thu, 09 Jan 2014 15:25:48 GMT

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

Re: Two dates in same table

Thu, 09 Jan 2014 15:28:56 GMT

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

Re: Two dates in same table

Thu, 09 Jan 2014 15:40:58 GMT

Brijesh & Henricc

Good to know someone else is doing the same thing. I get a lot of questions on why I do this so will bookmark this link for future reference.