# Halfhourly Timestamps in a Chart (Webi)

One of my colleagues had an interesting requirement (and I’m pretty sure many users might’ve), to show halfhourly timestamps in a chart, so they can show number of calls received to report issues every half hour (in a day) vs how many of them were resolved.

Updated (6/15/2016 8:44 AM EST): As few people suggested and/or requested, I’m updating this blog with another way to do this. Thanks to Amit Kumar, for the formula he suggested.

There are 2 ways of doing this, which means, 2 different formulas can be used to create the Halfhourly buckets.

1. Get the timestamp using the formula =Formatdate([Date timestamp];”HH:mm:ss”) and then, create the 48 different halfhourly timestamp buckets.
2. Get the timestamp using the same formula, convert each timestamp (every second) of a day to a numeric value and then, create 48 different halfhourly timestamp buckets.

Both solutions will work perfect.

Below is the data that we’ll be using during this exercise:

 Date Timestamp Calls Received Calls Resolved 6/12/2016 12:00:00 AM 9 8 6/12/2016 12:15:00 AM 2 1 6/12/2016 12:30:00 AM 3 2 6/12/2016 12:45:00 AM 3 2 6/12/2016 1:00:00 AM 4 3 6/12/2016 1:15:00 AM 5 4 6/12/2016 1:30:00 AM 7 6 6/12/2016 1:45:00 AM 3 2 6/12/2016 2:00:00 AM 8 7 6/12/2016 2:15:00 AM 4 3 6/12/2016 2:30:00 AM 7 6 6/12/2016 2:45:00 AM 7 6 6/12/2016 3:00:00 AM 3 2 6/12/2016 3:15:00 AM 6 5 6/12/2016 3:30:00 AM 6 5 6/12/2016 3:45:00 AM 7 5 6/12/2016 4:00:00 AM 3 1 6/12/2016 4:15:00 AM 2 0 6/12/2016 4:30:00 AM 4 2 6/12/2016 4:45:00 AM 8 6 6/12/2016 5:00:00 AM 4 2 6/12/2016 5:15:00 AM 8 6 6/12/2016 5:30:00 AM 4 2 6/12/2016 5:45:00 AM 7 5 6/12/2016 6:00:00 AM 8 6 6/12/2016 6:15:00 AM 4 1 6/12/2016 6:30:00 AM 5 2 6/12/2016 6:45:00 AM 4 1 6/12/2016 7:00:00 AM 4 1 6/12/2016 7:15:00 AM 8 5 6/12/2016 7:30:00 AM 4 0 6/12/2016 7:45:00 AM 7 3 6/12/2016 8:00:00 AM 5 1 6/12/2016 8:15:00 AM 5 1 6/12/2016 8:30:00 AM 6 2 6/12/2016 8:45:00 AM 8 4 6/12/2016 9:00:00 AM 4 0 6/12/2016 9:15:00 AM 8 4 6/12/2016 9:30:00 AM 4 0 6/12/2016 9:45:00 AM 8 4 6/12/2016 10:00:00 AM 4 3 6/12/2016 10:15:00 AM 7 6 6/12/2016 10:30:00 AM 4 3 6/12/2016 10:45:00 AM 4 3 6/12/2016 11:00:00 AM 5 4 6/12/2016 11:15:00 AM 8 7 6/12/2016 11:30:00 AM 3 2 6/12/2016 11:45:00 AM 3 2 6/12/2016 12:00:00 PM 6 5 6/12/2016 12:15:00 PM 4 3 6/12/2016 12:30:00 PM 7 6 6/12/2016 12:45:00 PM 5 1 6/12/2016 1:00:00 PM 7 3 6/12/2016 1:15:00 PM 7 3 6/12/2016 1:30:00 PM 7 3 6/12/2016 1:45:00 PM 6 2 6/12/2016 2:00:00 PM 3 2 6/12/2016 2:15:00 PM 7 6 6/12/2016 2:30:00 PM 3 2 6/12/2016 2:45:00 PM 6 5 6/12/2016 3:00:00 PM 3 2 6/12/2016 3:15:00 PM 7 6 6/12/2016 3:30:00 PM 3 2 6/12/2016 3:45:00 PM 7 6 6/12/2016 4:00:00 PM 3 1 6/12/2016 4:15:00 PM 7 5 6/12/2016 4:30:00 PM 3 1 6/12/2016 4:45:00 PM 7 5 6/12/2016 5:00:00 PM 6 4 6/12/2016 5:15:00 PM 6 4 6/12/2016 5:30:00 PM 2 0 6/12/2016 5:45:00 PM 7 5 6/12/2016 6:00:00 PM 6 4 6/12/2016 6:15:00 PM 4 2 6/12/2016 6:30:00 PM 7 5 6/12/2016 6:45:00 PM 5 3 6/12/2016 7:00:00 PM 7 2 6/12/2016 7:15:00 PM 7 2 6/12/2016 7:30:00 PM 3 2 6/12/2016 7:45:00 PM 6 1 6/12/2016 8:00:00 PM 3 2 6/12/2016 8:15:00 PM 7 2 6/12/2016 8:30:00 PM 3 3 6/12/2016 8:45:00 PM 6 1 6/12/2016 9:00:00 PM 3 3 6/12/2016 9:15:00 PM 7 2 6/12/2016 9:30:00 PM 3 2 6/12/2016 9:45:00 PM 7 2 6/12/2016 10:00:00 PM 3 2 6/12/2016 10:15:00 PM 7 6 6/12/2016 10:30:00 PM 3 2 6/12/2016 10:45:00 PM 7 6 6/12/2016 11:00:00 PM 6 4 6/12/2016 11:15:00 PM 6 5 6/12/2016 11:30:00 PM 2 0 6/12/2016 11:45:00 PM 6 4 6/12/2016 11:59:59 PM 9 2

Solution 1:

Step 1:

Create the below Dimension variables

• v_TimeStamp =FormatDate([Date Timestamp];”HH:mm:ss”) (to convert the Date Timestamp to HH:mm:ss format (24 hr format))

Updated (6/30/2016 3:57 PM EST): We can use a simple one line formula to replace the huge one with 40 If conditions. (Courtesy of Clemens Potter)

• v_Halfhourly Buckets (this creates 48 different buckets depending on the v_TimeStamp)

=FormatDate([Date Timestamp];”HH:”) + (If(FormatDate([Date Timestamp];”mm”) < “30”) Then “00:00” Else “30:00”)

(I’ve deleted the lengthy formula, which appeared here, earlier.)

Step 2:

1. Create a Column Chart, with v_Halfhourly Buckets on Category axis (X axis), Calls Received and Calls Resolved on Value Axis 1.
2. Go to Format Chart -> Value Axis -> in Stacking, select Unstacked
3. Go to Format Chart -> Plot Area -> Design -> update Spacing within Groups as 0.1 and Spacing between Groups to 0.5, that’ll make the bars for Calls Received and Calls Resolved for a Halfhourly Bucket look close to each other, which looks nice to compare the 2 values. Apply and OK.
4. In addition to that, Format Data Series of Calls Received as Red color and Calls Resolved as Green color.

Solution 2:
Step 1:

Create the below Dimension variables

• v_Date TimeStamp =ToDate(FormatDate([Date Timestamp];”M/d/yyyy HH:mm:ss”);”M/d/yyyy HH:mm:ss”) (to convert the Date Timestamp to 24 hr format)
• v_Hours =ToNumber(FormatDate([DateTimeStamp];”HH”)) (to get the Hour in 24 hr format in numeric datatype, we want it to be 24 hr format, so we don’t have to worry about AM or PM)
• v_Minutes =ToNumber(FormatDate([DateTimeStamp];”mm”)) (to get the mins in numeric datatype)
• v_Seconds =ToNumber(FormatDate([DateTimeStamp];”ss”)) (to get the seconds datatype)
• v_Numeric Value =([v_Hours]*3600)+([v_Minutes]*60)+[v_Seconds] (this creates a numeric value for every sec of the day (0 – 86399), as a day has 86400 secs, logic is simple, multiple Hours by 3600 as an hour has 3600 secs, multiple Minutes by 60 as a minute has 60 secs and add the Seconds)

Now, main variable of this logic

• v_Halfhourly Timestamps (this creates 48 different buckets depending on the v_Numeric Value)

=If([v_Numeric Value]  Between  (0;1799))  Then  “00:00:00”

ElseIf([v_Numeric Value]  Between  (1800;3599))  Then  “00:30:00”

ElseIf([v_Numeric Value]  Between  (3600;5399))  Then  “01:00:00”

ElseIf([v_Numeric Value]  Between  (5400;7199))  Then  “01:30:00”

ElseIf([v_Numeric Value]  Between  (7200;8999))  Then  “02:00:00”

ElseIf([v_Numeric Value]  Between  (9000;10799))  Then  “02:30:00”

ElseIf([v_Numeric Value]  Between  (10800;12599))  Then  “03:00:00”

ElseIf([v_Numeric Value]  Between  (12600;14399))  Then  “03:30:00”

ElseIf([v_Numeric Value]  Between  (14400;16199))  Then  “04:00:00”

ElseIf([v_Numeric Value]  Between  (16200;17999))  Then  “04:30:00”

ElseIf([v_Numeric Value]  Between  (18000;19799))  Then  “05:00:00”

ElseIf([v_Numeric Value]  Between  (19800;21599))  Then  “05:30:00”

ElseIf([v_Numeric Value]  Between  (21600;23399))  Then  “06:00:00”

ElseIf([v_Numeric Value]  Between  (23400;25199))  Then  “06:30:00”

ElseIf([v_Numeric Value]  Between  (25200;26999))  Then  “07:00:00”

ElseIf([v_Numeric Value]  Between  (27000;28799))  Then  “07:30:00”

ElseIf([v_Numeric Value]  Between  (28800;30599))  Then  “08:00:00”

ElseIf([v_Numeric Value]  Between  (30600;32399))  Then  “08:30:00”

ElseIf([v_Numeric Value]  Between  (32400;34199))  Then  “09:00:00”

ElseIf([v_Numeric Value]  Between  (34200;35999))  Then  “09:30:00”

ElseIf([v_Numeric Value]  Between  (36000;37799))  Then  “10:00:00”

ElseIf([v_Numeric Value]  Between  (37800;39599))  Then  “10:30:00”

ElseIf([v_Numeric Value]  Between  (39600;41399))  Then  “11:00:00”

ElseIf([v_Numeric Value]  Between  (41400;43199))  Then  “11:30:00”

ElseIf([v_Numeric Value]  Between  (43200;44999))  Then  “12:00:00”

ElseIf([v_Numeric Value]  Between  (45000;46799))  Then  “12:30:00”

ElseIf([v_Numeric Value]  Between  (46800;48599))  Then  “13:00:00”

ElseIf([v_Numeric Value]  Between  (48600;50399))  Then  “13:30:00”

ElseIf([v_Numeric Value]  Between  (50400;52199))  Then  “14:00:00”

ElseIf([v_Numeric Value]  Between  (52200;53999))  Then  “14:30:00”

ElseIf([v_Numeric Value]  Between  (54000;55799))  Then  “15:00:00”

ElseIf([v_Numeric Value]  Between  (55800;57599))  Then  “15:30:00”

ElseIf([v_Numeric Value]  Between  (57600;59399))  Then  “16:00:00”

ElseIf([v_Numeric Value]  Between  (59400;61199))  Then  “16:30:00”

ElseIf([v_Numeric Value]  Between  (61200;62999))  Then  “17:00:00”

ElseIf([v_Numeric Value]  Between  (63000;64799))  Then  “17:30:00”

ElseIf([v_Numeric Value]  Between  (64800;66599))  Then  “18:00:00”

ElseIf([v_Numeric Value]  Between  (66600;68399))  Then  “18:30:00”

ElseIf([v_Numeric Value]  Between  (68400;70199))  Then  “19:00:00”

ElseIf([v_Numeric Value]  Between  (70200;71999))  Then  “19:30:00”

ElseIf([v_Numeric Value]  Between  (72000;73799))  Then  “20:00:00”

ElseIf([v_Numeric Value]  Between  (73800;75599))  Then  “20:30:00”

ElseIf([v_Numeric Value]  Between  (75600;77399))  Then  “21:00:00”

ElseIf([v_Numeric Value]  Between  (77400;79199))  Then  “21:30:00”

ElseIf([v_Numeric Value]  Between  (79200;80999))  Then  “22:00:00”

ElseIf([v_Numeric Value]  Between  (81000;82799))  Then  “22:30:00”

ElseIf([v_Numeric Value]  Between  (82800;84599))  Then  “23:00:00”

ElseIf([v_Numeric Value]  Between  (84600;86399))  Then  “23:30:00”

Step 2:

1. Create a Column Chart, with v_Halfhourly Timestamps on Category axis (X axis), Calls Received and Calls Resolved on Value Axis 1.
2. Go to Format Chart -> Value Axis -> in Stacking, select Unstacked
3. Go to Format Chart -> Plot Area -> Design -> update Spacing within Groups as 0.1 and Spacing between Groups to 0.5, that’ll make the bars for Calls Received and Calls Resolved for a Halfhourly Timestamp look close to each other, which looks nice to compare the 2 values. Apply and OK.
4. In addition to that, Format Data Series of Calls Received as Red color and Calls Resolved as Green color.

Voila, we did it. This is what the chart looks like, now.

Please try it and let me know your feedback, so I can fix if there are any issues or improve it further.
Thanks,
Mahboob Mohammed

### Assigned Tags

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

have you tried with this ?

=If(FormatDate([Date Timestamp];"HH:mm:ss")) Between ("00:00:00";"00:30:00") Then "00:30:00"  ElseIf(FormatDate([Date Timestamp];"HH:mm:ss")) Between ("01:00:00";"01:30:00") Then "01:30:00" Elseif ....

Mahboob Mohammed
Blog Post Author

Hi Amit,

I tried just the FormatDate([Date TimeStamp];"HH:mm") and then did it differently as I wanted to do it in a different way. But, I'll think about it and may be edit the blog post.

Mahboob Mohammed

This will come handy Mahboob. I think Amit has a great point, may be you should add a 2nd way of doing this with a different formula (the formula that Amit has).

Ray Khan

Mahboob Mohammed
Blog Post Author

Thanks for stopping by and the suggestion Ray Khan!!!

That update is coming soon, I'll update the blog soon to have 2 different ways of doing it, basically using 2 different formulas.

Mahboob Mohammed

Nice effort Mahboob!! but will it have some performance impact?

Regards

Niraj

Mahboob Mohammed
Blog Post Author

Hi Niraj,

48 conditions shouldn't be bad at all.

Thanks for stopping by,

Mahboob Mohammed

Mahboob Mohammed you have shown us a new gate for this issue. Great effort.

But still I fill it should be done in another way(Something like Amit Kumar

Mahboob Mohammed
Blog Post Author

Thanks for the compliment and suggestion KD!!!

That update is coming soon, I'll update the blog soon to have 2 different ways of doing it, basically using 2 different formulas.

Mahboob Mohammed

Mahboob Mohammed
Blog Post Author

Everyone,

I've updated the blog, now it has 2 solutions. 1st is what Amit Kumar suggested and, 2nd is an unconventional way that I adopted.

Mahboob Mohammed

Nice effort Mahboob Mohammed.

Thanks for sharing it.

Thanks,

Jothi

Mahboob Mohammed
Blog Post Author

Mahboob Mohammed

Thanks for sharing Mahboob, very clear and easy to understand.

Mahboob Mohammed
Blog Post Author

Mahboob Mohammed

Hi Mahboob,

In order to avoid many "ElseIf"s, can you try to concatenate the hours with "00:00" or "30:00" like this:

=FormatDate([Date Timestamp];"HH:") + (If(FormatDate([Date Timestamp];"mm") < "30") Then "00:00" Else "30:00")

Best Regards

Clemens

Mahboob Mohammed
Blog Post Author

Hi Clemens,

That's a great idea, will update the blog.

Thanks for stopping by,

Mahboob Mohammed

Hi ,

Brilliant report, was able to follow instruction and compile the graph.

Now my boss was so impress , he require additional information too also display open/backlog ticket.

I tried a couple of ways and had no luck.

Have anybody else had the same requirements and were able to resolve?

Mahboob Mohammed
Blog Post Author

Hi Eugene,
Do you want to display additional bars for open/backlog tickets? Please elaborate.
Thanks,

Mahboob Mohammed

Hi,

As an additional line graph with open and closed tickets as bars.

I tried formula: (calls received + previous open tickets)- calls resolved = open tickets

 Date Timestamp Calls Received Calls Resolved Open tickets 6/12/2016 12:00:00 AM 9 8 1 6/12/2016 12:15:00 AM 2 1 2 6/12/2016 12:30:00 AM 3 2 3
Mahboob Mohammed
Blog Post Author

Hi Eugene,

So, what happened? Were you not able to get it? Or the Open Tickets calculation is showing you wrong numbers? I don't understand what is your question here.

Thanks,
Mahboob Mohammed

Hi,

As per excel below- I am unable to get the open tickets calculation correct.

Formula: Open Bal + Inc Submit - Inc closed = Open Tickets
Balance of open tickets brought forward to next month as Open Bal.

Mahboob Mohammed
Blog Post Author

Hi Eugene,

Please let me know the variable names and the formulas you created in Webi for Open Tickets? Are you just calculating all that in one variable or more? If this is getting into a long conversation, you might want to create a question in WebiDeski Questions and Answers page.