Jump to content

Filter by time frame and display total


aromero
Go to solution Solved by David Beagan ,

Recommended Posts

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!

excel_report_example.jpeg

current_status_sapr.jpeg

Edited by aromero
Link to comment
Share on other sites

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.

current_status_data.jpeg

Link to comment
Share on other sites

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)

  • Like 1
Link to comment
Share on other sites

  • Solution

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 

image.thumb.png.ded6ce785ffa0cb1d33498b674790db2.png

Edited by David Beagan
  • Like 2
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
  • Create New...