Question Re: Time Interval Question in Technology Q&A

Time Interval Question

evanc — Tue, 29 Sep 2015 15:40:10 GMT

Hi all,

This may be a basic question, but I am trying to make a time interval for a visualization project I am doing. The specific interval I am using for this example is an every thirty minute interval. I want to plot the number of actions vs. the time intervals, and then look back at the data for previous days at the same time interval.

However, if a certain day (in this example 8/31/15) did not have any actions during a 30 minute interval, my variable will not create/display that time interval.

In this figure, the times for 6 - 6:30 PM and 6:31 - 6:59 PM are a good example of what is missing. How can I create a variable that will allow me to plot a figure like this where the value is 0 for the times where there was no action?

[q30m] =If [Minutes] Between (0;30) Then ([Hours]+" - "+[Hours]+":30 "+[AM-PM]) Else If [Minutes] Between (31;59) Then ([Hours]+":31 - "+[Hours]+":59 "+[AM-PM])

The [Minutes], [Hours], and [AM-PM] variable I created from a date/time stamp of actions. I understand why it is not creating an interval currently, but I can't come up with a workaround.

Please let me know if this doesn't make sense!

Best,

Evan

Re: Time Interval Question

Former Member — Wed, 30 Sep 2015 08:14:42 GMT

Evan,

Can you help in understanding on how you are comparing the data of current day and previous day?

Your formula contains objects as [Hours] and [Minutes]; however, there is no comparison of time intervals.

Is same query fetching intervals for both current and previous day or are there two different data providers?

Could you please elaborate a bit?

Regards,

Yuvraj

Re: Time Interval Question

evanc — Wed, 30 Sep 2015 20:11:54 GMT

Sorry for the delay!

I'm using one query, and the same dimension from the query has all of the date/time values of the actions. I'm trying to roll that up into the thirty minute intervals.

I'm using RelativeValue() to get the previous day's time interval, which works well, except when the current day is missing a time interval (because it won't show me the previous day if the current day doesn't exist)

Re: Time Interval Question

amitrathi239 — Wed, 30 Sep 2015 20:45:18 GMT

have you tried with Timedim() function to get the continous dates.

Amit

Re: Time Interval Question

evanc — Wed, 30 Sep 2015 23:38:46 GMT

I have not tried the TimeDim() function, but there's always an action on each day, it's just when the action(s) are during the day.

Re: Time Interval Question

Former Member — Thu, 01 Oct 2015 00:38:03 GMT

Hi Evan,

One solution would consist on merging your [q30m] variable with a new dimension (call it X) coming from another provider (e.g. a flat file or xls source) that contains all the possible intervals.

That is, new provider must contain a row for each possible interval you want in your x-axis and it will also contain X and another field (say "num") with the constant 0.

For example if you want to graph from 6 am to 11 pm then create a xls containing ...

X num

----------------- ------

6 - 6:30 AM 0

6:31 - 6:59 AM 0

7 - 7:30 AM 0

...

10 - 10:30 PM 0

10:31 - 10:59 PM 0

(Total: 34 rows)

... and create the corresponding provider in webi.

- Merge [X] with [q30m]. Notice q30m must be dimension type variable.

- Create a new [Actions_2] variable type measure with the following formula: [Actions] + [num]

Graph using the merged dimension and the new measure.

This technique should provide the solution to fill the gaps.

Regards,

Fernando

Re: Time Interval Question

evanc — Thu, 01 Oct 2015 12:12:42 GMT

Hi Fernando,

I had thought about this before, but I was under the impression that I can't merge anything with a variable. Am I mistaken?

Re: Time Interval Question

Former Member — Thu, 01 Oct 2015 20:15:41 GMT

Evan, you're absolutely right. I was missing that basic fact

Still we can make the technique work by taking the [q30m] calculation to the database or the universe level. That way we would not need to create varialbes and we would be merging directly on two provider fields, not involving any variables.

Aside, maybe it would also be easier to define simpler values on the xls and the other source and to do the formatting on the new merged dimension in webi. I mean the DB / universe and the excel fields would be strings like

0600

0630

0700

...

instead of

6 - 6:30 AM

6:31 - 6:59 AM

7 - 7:30 AM

...

and the formatting to "6 - 6:30 AM" would be done in webi using the merged dimension.

Re: Time Interval Question

former_member184594 — Fri, 02 Oct 2015 07:54:38 GMT

Hi Evan,

Fernando is right about this. It is better do this grouping in the universe level. So that, you can merge the objects.

If you are using BW as your reporting database then you are going to have to do it in InfoProvider level when you are extracting the data to the InfoProvider.