Halfhourly Timestamps in a Chart (Webi)

mhmohammed · ‎06-14-2016

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.

Get the timestamp using the formula =Formatdate([Date timestamp];"HH:mm:ss") and then, create the 48 different halfhourly timestamp buckets.

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.ptter)

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:

Create a Column Chart, with v_Halfhourly Buckets on Category axis (X axis), Calls Received and Calls Resolved on Value Axis 1.

Go to Format Chart -> Value Axis -> in Stacking, select Unstacked

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.

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:

Create a Column Chart, with v_Halfhourly Timestamps on Category axis (X axis), Calls Received and Calls Resolved on Value Axis 1.

Go to Format Chart -> Value Axis -> in Stacking, select Unstacked

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.

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

Halfhourly Timestamps in a Chart (Webi)

SAP PI for Beginners

ABAP 7.40 Quick Reference

Fiori: technical installation and configuration of one app from A - Z