The Basics about Dates, Date Ranges and Set Analysis


    One of the things I've learned is that having a Master Calendar makes life easier, allows the user more to select in a flexible way different year, date, month analysis, and allows the developer to create new dimensions related to temporal analysis quickly. But this is only the starting point.

    Using variables is a very helpful and easy way to save time creating parallel calendars or logical islands, keeping cleaner the application.

    Here are three simple charts using a fact table, a master calendar and a few variables to get a lot of different stuff for different analysis in an easy way.

    Finally, set analysis is the faster way to get the charts rendered. There are some useful hints on how to use variables and dates in set analysis.

    The first chart shows how having a master calendar the user can select some values providing a date range (starting and ending dates).

    The second is a simple year to date chart using a flag field in the load script and set analysis to get the data.

    The third gives more flexibility when it comes to select different, non consecutive years.

    I hope you find it useful, not to mention that all suggestions are welcome and thanks to you we can improve the document with more complex formulas and analysis. So please, comment!