Tutorial - Using Common Date Dimensions and Shared Calendars

[rwunderlich]

The question of aggregating multiple dates into a common date dimension like Month comes up frequently on this forum. I've posted a QVW tutorial on my website that shows how I typicaly handle the issue.

http://qlikviewcookbook.com/recipes/download-info/tutorial-using-common-date-dimensions

The QVW is not Personal Edition enabled.

-Rob

Message was edited by: Rob Wunderlich Link updated

[dadumas]

Why not always just use a date island for all dates, and forget any use of calendars that are connected to data?

Also, assumes measures are always referenced as variables in any UI objects. 

Variables Examples:  Note: DateID represents the floor(Date) column in the date island calendar, loaded using script code.

vSales:

Sum( {<Sales_Date={">=$(=Min(DateID))  <= $(=Max(DateID))"}  >} Sales)

vPurchases:

Sum( {<Purchase_Date={">=$(=Min(DateID))  <= $(=Max(DateID))"}  >} Purchases)

vCountInvoices:   InvoiceCounter is always 1 in the fact records

Sum( {<Invoice_Date={">=$(=Min(DateID))  <= $(=Max(DateID))"}  >} InvoiceCounter)

Dave

[dadumas]

I have also posted this reply to Rob, so forgive the duplicate reply post.  Would love to get your opinion as well.

Why not always just use a date island for all dates, and forget any use of calendars that are connected to data?

Also, assumes measures are always referenced as variables in any UI objects. 

Variables Examples:  Note: DateID represents the floor(Date) column in the date island calendar, loaded using script code.

vSales:

Sum( {<Sales_Date={">=$(=Min(DateID))  <= $(=Max(DateID))"}  >} Sales)

vPurchases:

Sum( {<Purchase_Date={">=$(=Min(DateID))  <= $(=Max(DateID))"}  >} Purchases)

vCountInvoices:   InvoiceCounter is always 1 in the fact records

Sum( {<Invoice_Date={">=$(=Min(DateID))  <= $(=Max(DateID))"}  >} InvoiceCounter)

Dave

[rwunderlich]

Date Islands have their uses, but drawbacks as well. Performance and expression complexity are two drawbacks that come to mind. 

In your example above, how do you produce a chart that has a Dimension of Month and shows Sales, Purchases & Invoices?

-Rob

[dadumas]

Well, the complexity of set analysis is only complex to the extent to of the ">= and <=" in the set expression.  In regard to performance, creating an all inclusive "cannonical bridge table of transactions and dates" would seem to me to use more memory (a bridge table of records with all combinations transactions using the dates).   In addition, this also adds another join bridging the facts to a cannonical calendar. I always thought that "less hops" are better in QlikView.  Not sure which would perform better, date Island or Cannonical bridge.

Producing a Chart that uses the "Month" dimension would be sourced from the Date Island, and thus using measures for Sales, Purchases, and Invoices should work fine, as they would reference DateID in their variable definitions.  Selection for Year, Month, Quarter, etc would all come from the same date island. 

I do see situations where multiple "aliased" calendars would let users analyze Sales, Purchases, and Invoices data across different "role playing" calendars.  Ex. SalesCalendar, Purchase Calendar, InvoiceCalendar.

Other than that I am still struggling with the need to create a connected cannonical calendar in lieu of the flexibility of a date island calendar.

Hopefully I have explained this correctly - always looking for the best solution for dates.

[rwunderlich]

If it's working for you, great. That is the objective and what's important.

While the bridge table may use more RAM, it's typically a trivial amount. The connected tables allow the associative engine to do the work. That should be much faster than building disconnected sets for every expression at chart calc time.

-Rob

[dadumas]

Rob, you are correct.  The date island has no dimensionality when used in a chart against measures that filter against it.  The totals are always correct, so for text box summary objects like "Sales To Date" it works.  Thanks!

[hic]

The number of hops affect performance a lot less than most people think. See A Myth about the Number of Hops.

Rob is absolutely right that a disconnected data island is slower than connected tables: If you use a logical island, then you need a chart expression that manually matches the values of the logical island with the values of the main data model. And this will always be slow.

HIC

[MayilVahanan]

Hi Rob and Henric,

I think , the embedded license is expired.

Could you please provide qvs file to understand the concept.

Its help others too.

[dadumas]

That makes sense now.  So I have come full circle.  Better to use a combination of the cannonical master calendar with the bridge table, and optional multiple role specific calendars for more detailed analysis of measures across those.  I really appreciate the responses.  Thanks!

[hic]

I don't see any big advantage compared to the solution with a canonical date. A canonical date is more performant (see below http://community.qlik.com/message/707764#707764) and allows a logical data model where links between tables are automatically evaluated, and not defined in expressions.

When I have used logical islands, I have used them as dimension in charts, and used expressions to match the data model date with the date from the logical island. But from your expressions, I can say that that is not what you are doing.

Anyway, using a canonical date as dimension in a chart allows the user to click in the chart to make a selection in the date dimension. If you use a logical island, you must prevent the user from making selections in the dimension, as they otherwise would display results that are wrong.

HIC