How to generate reports/KPIs from data

Hi guys,

I've been listing all of the cases raised within my department for the past couple of months. I am now trying to do the following;

Show the number of issues raised each day
Number of issues open at the end of each day
Number of issues open that are older than a week

The columns I've got are; Date of issue, time of issue, raised by, department, issue, topic, date completed, time completed, completed by, status.

I really am a bit clueless in terms of how to do this. I know I can run some pivot tables and charts to show open vs closed issues, but I don't know what to do with my data to get the result I want!

Any help or advice would be appreciated.

Thanks,

Rich

IT KPIs2.xlsx

Please see the attached. I think I've got the hang of number of issues per day. Number of issues open at the end of each day has me stumped! And number of issues open older than a week, I can see you can filter the pivot table to have certain dates, but I can't see how I can do a formula to look at today's date and older than 7 days.

Thanks!

Rich

Sorry for the delayed response! Thanks so much for your help on this. I'm still struggling on part of this though!

Originally Posted by daffodil11

EOD count is pretty simple:

click your Pivot Table, click Options tab, then "Fields, Items, & Sets", choose Calculated Field

I called mine "Open@EOD"

Formula:
=('Date Completed' >'Date of request' )

I've managed to create the calculated field successfully. However, when I have the date completed being greater than the date request, the maximum value I can have is one. This is a problem, as there could be a day where two or more requests are open.

For example:

Date of Request is 02/01/2015. I could have four rows where the Date Completed is 06/01/2015. In theory, the EOD should show a value of 4 for that day. However it shows 1. I have tried setting the summarize value as sum, but this didn't make a difference.

Originally Posted by daffodil11

As for the "still open within the last 7 days", you might be best served by just adding a column to your existing data since Pivot Tables don't support volatile functions like Now() and Today().

Column L
=([@[Date of request]=]=>(TODAY()-7))*([@Open]=1)

And then just sum that in your Pivot as well.

I have added this column with the formula. All values are showing as false. Do you know what might cause that?

Thanks so much for the help.

Haha it does indeed!

Yeah I've updated the values.

I changed one of the dates of an open issue from November 20th 2014 to 06th January 2015, but that didn't update to true. Not sure why it wouldn't be setting the value to true?