Time Interval Question

[evanc]

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

[Former Member]

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

[evanc]

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?

[Former Member]

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.

[former_member184594]

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.