De-mystifying the TimeDim function

The Timedim function was introduced to Webi 3.1 (SP3?) as a way to create a consecutive range of dates when the source date dimension returns a non-consecutive range.

This is more easily demonstrated than explained!

Using the Island Resorts sample Universe, create a simple report which returns the Reservation Dates for the customer ‘Piaget’:

As we can see, this customer has made two reservations in August 2007, and the dates are non consecutive.

By using the TimeDim function, we imply a range of dates between, or filling in, the range of dates returned by the date dimension:

When using TimeDim we can provide one of four operators – DayPeriod, MonthPeriod, QuarterPeriod or YearPeriod, to determine the range of dates returned. The default (which can be omitted as above) is DayPeriod and returns a consecutive range of dates by day. MonthPeriod is best explained with another Customer – choose ‘Baker’:

When we use the MonthPeriod operator, a range of dates consecutive by month is returned:

Choose customer ‘Oneda’ to demonstrate QuarterPeriod:

.. which, as you’d expect, returns dates by quarters:

Finally, the YearPeriod operator should hold no surprises by now:

It’s simple enough to wrap the TimeDim function in another function. For example, we can dispense with the dates as above and just show the year by using:

…and we can fill in any blanks in our missing columns by using a function like:

So what are the caveats for using TimeDim? The dimension you use needs to be a Date Object which is from a data provider – it can’t be a variable.

Let’s see how this can work in real life. If we remove the Customer filter from the example query and just return all data, we should get something like:

If we graph a count of customers by the Year and Quarter of the Reservation Date:

=count([Customer])

=year([Reservation Date])

=Quarter([Reservation Date])

…we get:

As you can see, we have missing quarters in 2008 and 2009.

If we replace the Year and Quarter date objects with their TimeDim equivalents:

=count([Customer])

=Year(TimeDim([Reservation Date];YearPeriod))

=Quarter(TimeDim([Reservation Date];QuarterPeriod))

…then our graph is a lot more acceptable:

I hope this helps de-mystify the TimeDim function a little and helps to demonstrate its usage!