De-mystifying the TimeDim function

[former_member201488]

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!

[Former Member]

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?

[former_member201488]

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?

[Former Member]

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.

[former_member201488]

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.

[Former Member]

Thanks for the quick response. It would be nice if this function worked on variables.

[Former Member]

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.

[#8]

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

[former_member201488]

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?

[#10]

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

[former_member201488]

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:

However, if I use:

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

..I get expected results. :???:

[#12]

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.

[former_member201488]

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

[#14]

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

[mhmohammed]

Thanks Neil!!! This is great.

[Former Member]

Hi, plz i need your help i have the same problem could someone find the solution of displaying multiplevalues when we use the function timedim with period week

@hashir ahmed any solutions ?

mhmohammed 

[mhmohammed]

Hi lo bn,

Would you please post you question in the Webi Questions blog? Many other gurus can help you.

https://answers.sap.com/tags/907900296036854683333078008146613#

Thanks,
Mahboob Mohammed

[mhmohammed]

Hi lo bn,

I would use Context operators to do that. In, ForEach, ForAll, combine it with other variables and flag objects.

Would you please post you question in the Webi Questions blog? Many other gurus can help you.

https://answers.sap.com/tags/907900296036854683333078008146613#

Thanks,
Mahboob Mohammed

[nscheaffer]

This is FANTASTIC!