topic Date Dimension Help in QlikView

Date Dimension Help

stuwannop — Fri, 29 Jun 2012 13:48:05 GMT

I'm looking for a bit of help around date dimensions - here's the scenario.

I have a table called "Support Calls" and there are two dates in it - createdon and closedon. I have created a calendar table defining dates such as fiscal year, month, month/year etc. At present I have only linked the calendar script/table to the createdon field but I want to be able to link it to both createdon and closedon so the user can select one date and see how many calls were created and how many were closed on that day,month or whatever.

I suspect it's something to do with a link table but I'm quite new to linked tables so I was hoping for some pointers. Can anyone help?

Thanks in advance.

Stuart

Re: Date Dimension Help

Jason_Michaelides — Fri, 29 Jun 2012 14:46:17 GMT

Search this forum for use of IntervalMatch(). That will do what you want.

Hope this helps,

Jason

Re: Date Dimension Help

stuwannop — Mon, 02 Jul 2012 15:14:42 GMT

I've had a read up on Intervalmatch and I'm not sure if it's what I want (either that or I don't understand it properly).

The intervalmatch, by the looks of it, would show me all calls between two dates but that's not really what I'm looking for. What I want to do is this:

ID, createdon, modifiedon

123,01/08/2012,01/08/2012

1234, 30/07/2012, 01/08/2012

So if I selected 01/08/2012 from a master calendar and then counted the number of calls modified on that date it would return 2, the number of calls created would be 1. I'm not sure how I can get intervalmatch to work for this?

thanks

Stuart

Re: Date Dimension Help

Jason_Michaelides — Mon, 02 Jul 2012 19:26:32 GMT

Sorry Stuart - I misunderstood before. Yes, you will need a link table. Try this in your script:

SupportCalls:

Load

ID,

Other fields ...not dates,

From...SupportCalls data;

Link:

Load

ID,

ModifiedDate AS KeyDate,

'Modified' AS DateType

From...Support calls data;

Concatenate (Link)

Load

ID,

CreatedDate AS KeyDate,

'Created' AS DateType

From...Support calls data;

Calendar:

Load.....;

Now, to get calls created in a chart, your expression should be:

Count({<DateType={Created}>} Distinct ID)

Note: if you have a large data set then extract the IDs into a separate table with a counter column and use SUM() instead of Count(Distinct) for better performance.

Hope this helps,

Jason

Re: Date Dimension Help

stuwannop — Wed, 04 Jul 2012 09:54:51 GMT

Thanks again Jason I probably haven't been clear on what I wanted! - I'll give that a whirl and let you know how I get on.

Stu

Re: Date Dimension Help

Wed, 04 Jul 2012 10:06:40 GMT

Hi stuwannop,

try this->

LOAD ID AS [createdon ID],

createdon AS Date

from table.qvd;

CONCATENATE

LOAD ID AS [modifiedon ID],

modifiedon AS Date

from table.qvd;

Calendar:

LOAD Date

from Calendar;

*************************************************

then count( [createdon ID]) to count createdon date's IDs

and

count( [modifiedonID]) to count modifiedon date's IDs.

After selection of a date from the calendar you will get both IDs related to that date.

Hope it will help.

-Regards

Apurva

Re: Date Dimension Help

stuwannop — Wed, 04 Jul 2012 13:08:43 GMT

Thanks for your help guys - with your help I've just created my first link table - better still, I understand it!

Many thanks and have a good day.

Stu

Re: Date Dimension Help

Jason_Michaelides — Wed, 04 Jul 2012 14:07:03 GMT

No problem. As a last bit of advice, don't use link tables unless you absolutely have to. By their very nature they introduce more joins and although sometimes they are necessary, I would always try and find a traditional method first. Although, like everything with QlikView you need to balance any other solution as it may be worse!