Filter by time frame and display total

[aromero]

Hi everyone,

I am looking to create a report for a ticketing system. The report must show pending and completed tickets by specific times. Please check screenshot attached of what I am looking for created in excel. So far, I can get the total count per day, but I am unable to filter by specific time frame. I tried filters, added a parameter field list, added a calculation without luck. Please provide me with some information or resources I can verify to accomplish this. 

Thank you!

Edited July 8 by aromero

[David Beagan]

When you say "filter by specific time frame" are you referring to creating the columns 7am   11am   2pm  5pm ? 

[aromero]

Yes, I would like to create the columns and display the count of tickets. for example, in column 7am display the count of tickets from 7am until 11am, for column 11am tickets from 11am until 2pm and so on. I attached a screenshot with the data I am working on.

[Patrick Huebgen]

17 hours ago, aromero said:

Yes, I would like to create the columns and display the count of tickets. for example, in column 7am display the count of tickets from 7am until 11am, for column 11am tickets from 11am until 2pm and so on. I attached a screenshot with the data I am working on.

 

I understand that you want to create a group that combines the hours into a new field that you could use in horizontal or vertical sorting.

If that's the case look into the DTPART function - you can add it to extract the hour from your field - should look like  DTPART(REQUESTED_ON, HOUR)

[Patrick Huebgen]

By the way to see more samples of this and other functions please check https://docs.tibco.com/pub/wf-wf/9.2.4/doc/pdf/IBI_wf-wf_9.2.4_functions.pdf?id=13

[David Beagan]

I made  a sample data set, current_open_requests, to try this.  

The basic approach to producing this kind of report is to calculate a TIMERANGE  field based on the REQUESTED_ON field. Then use that TIMERANGE field to make ACROSS columns. The following code illustrates this.

 SET PAGE=NOLEAD
 DEFINE FILE current_open_requests
 HOUR/HH      =  REQUESTED_ON;
 HOURA5/A5    = FPRINT(HOUR,'HH','A5');
 TIMERANGE/A7 = DECODE HOURA5 ('07' '  7am' '08' '  7am' '09' '  7am'  '10' '  7am' '11' '11am' '12' '11am' '13' '11am' 
                               '14' ' 2pm'  '15' ' 2pm'  '16' ' 2pm'   '17' ' 5pm'  '18' ' 5pm' '19' ' 5pm'  ELSE 'other');
 END
 
 TABLE FILE current_open_requests
 SUM CNT.SEQ_NO
      BY STATUS   AS ''
 ACROSS TIMERANGE AS ''
 ON TABLE SET PAGE NOLEAD
 END

The output from this code 

Edited July 10 by David Beagan

[aromero]

Thank you so much! Exactly what I was looking for.

[David Beagan]

The columns in my example are not sorted in quite the right order. You can change that by using extra leading spaces on the decoded values.