Date Dimension Help

[stuwannop]

Hi

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

[Jason_Michaelides]

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

Hope this helps,

Jason

[stuwannop]

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

[Jason_Michaelides · Accepted solution]

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

[stuwannop]

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

[#6]

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

[stuwannop]

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

[Jason_Michaelides]

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!