Master Calendar for multiple event dates

[salleninsd]

Hello,

I am trying to add a Master Calendar to my model, and the challenge is that there are many different 'event' dates that occur in my data set.  We have Referral, Start-of-Care, Non-Admission, Visit, EpisodeStart, EpisodeEnd, Budget dates, etc.  The main fact tables are the Episode and Visit tables.  The model is working okay, but I want to optimize and have the ability to filter on a master time dimension.  In the current state, the connections are creating 3 synthetic tables.

I am looking for some suggestions on the overall structure.  Most everything is driven off of the StartOfCare date, but I also want to report Referrals and NonAdmissions on the same timeline.  I am posting in the Healthcare Group, as I'm sure you all have encountered some of the same issues.

Any thoughts are most appreciated

[swuehl]

In general, you can achieve a common time line using a canonical date.

Canonical Date

I can't really see your data model (too small letters for this time of the day), but another option could be a data island calendar (a table not connected with any other table), and then using set analysis to filter the time dimensions with the selected time range from the calendar.

[salleninsd]

Thank you so much, Stefan.  This is kind of what I was thinking, but didn't come across the article that you referenced in your post.

I really appreciate you taking the time to reply and share.

Take good care!

[rwunderlich · Accepted solution]

Although HIC's blog post explains it well,  this example provides a script pattern for implementing canonical dates.

Qlikview Cookbook: Tutorial - Using Common Date Dimensions http://qlikviewcookbook.com/recipes/download-info/tutorial-using-common-date-dimensions/

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

http://www.easyqlik.com

[bvssudhakar]

Hi Scott Allen,

Can you please share your qvw and qvd. So that i will try to help you

Regards,

Sudhakar

[salleninsd]

Hi Sudhakar,

Thank you for responding!  Unfortunately, the qvds are on our Enterprise server and are protected under HIPAA, and I cannot share.  I could only do a visual snapshot of the model .

Take good care!