Skip to Content

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’:

Timedim1.JPG

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

Timedim2.JPG

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

Timedim3.JPG

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’:

TimeDim4.JPG

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

TimeDim5.JPG

Choose customer ‘Oneda’ to demonstrate QuarterPeriod:

Timedim6.JPG

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

Timedim15.JPG

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

Timedim7.JPG

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:

Timedim8.JPG

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

Timedim9.JPG

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:

Timedim11.JPG

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:

Timedim13.JPG

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:

Timedim14.JPG

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

To report this post you need to login first.

14 Comments

You must be Logged on to comment or reply to a post.

  1. Erik Schefstad

    Thank you for the post- this is very helpful. Have you found any way to have a continuous time dimension by ‘Week’? Many of our reports require data by week (months are not detailed enough… but days really clutter the report). Perhaps merging the data with a calendar table?

    (0) 
    1. Neil Mitchell-Goodson Post author

      You can wrap the Timedim function in another date related function (week, month, year), if that helps?

      If you have a calendar table already, I’d argue that you possibly don’t need to use the Timedim function.

      By the sounds of it, you might need to just use a Week function in your report anyway, if daily data is too granular?

      (0) 
      1. Hashir Ahmed

        Hi Neil,

        TimeDim() has DayPeriod, MonthPeriod, QuarterPeriod, YearPeriod but no WeekPeriod and I guess Erik was referring to missing WeekPeriod.

        Do you know a work around for that?

        Thanks!

        Hashir

        (0) 
  2. Gaurav Agarwal

    Thank you for the post.

    In your first screenshot, the reservation date returned was 04/08/2007 and 23/08/2007. Let’s say, you add reservation date as a prompt and the user entered the prompt values as Start Date: 01/08/2007 and End Date: 31/08/2007.

    Can you use the TimeDim function to fill reservation dates between the prompt dates? When I tried it, it fills all dates between min reservation date and max reservation date. Any help would appreciated.

    Thanks.

    (0) 
    1. Neil Mitchell-Goodson Post author

      Sadly not – the report will return the reservation dates falling between those prompt values – hence the min and max reservation dates being returned. The TimeDim function only works with dimension data.

      One solution would be to use a calendar table and merge these dates with the reservation dates – but again, there should be no need to use the TimeDim function then.

      (0) 
  3. Mark Prosser

    Neil, thanks for clearing that up. I’ve not had to use it yet because I’ve always included a calendar table in my databases but it’s useful to know what can be done if there are restrictions on what can be changed on the database side.

    (0) 
  4. Neil Mitchell-Goodson Post author

    Only to use a Week function with Timedim; if you use Timedim at the lowest granularity (dayperiod) then wrap this in a week function, it should approximate this, no?

    (0) 
    1. Hashir Ahmed

      If I write something like this:

      Week(TimeDim(DateField; DayPeriod)

      The output is not aggregated on Weeks rather the granularity is still on day level and multivalue error is displayed i.e.

      Week                          NetSales

      multivalue        1000

      multivalue         1200

      However, if Day level granularity isadded to output only then you get the weeks, i.e.

      Week       Day      NetSales

      1             1                 1000

      1             2                 1200

      (0) 
      1. Neil Mitchell-Goodson Post author

        Just checking this out, I came across the weekperiod operator in the formula help (which wasn’t in the documentation). It doesn’t work how you’d expect though and produces a #multivalue error as described above.

        So if I try:

        =timedim([Date];weekperiod)

        I get this:

        td1.JPG

        However, if I use:

        =week(timedim([Date];weekperiod))

        td2.JPG

        ..I get expected results. 😕

        (0) 
        1. Hashir Ahmed

          Thanks Neil! The given solution works if I import data from Excel into Webi but does not work on report based on Bex Query using BICS, I am on BOBJ 4.0 SP10.

          Week.png

          (0) 
          1. Neil Mitchell-Goodson Post author

            Sorry dude, works for me on XI R3.1 / SQL Server; I don’t use BICS so can’t provide further assistance with this; from your screeshot though it looks like it returns some values instead of an across the board error, so you never know – persist in your attempts and you may reach a solution! Good luck 😉

            (0) 
            1. Hashir Ahmed

              I can get the following output if I replace WeekPeriod with DayPeriod but now I only want to display the result row of table.

              Week.png

              (0) 

Leave a Reply