Pivot table

**nmss18** · 03-19-2010, 11:56 AM

Hello,
I am doing some data analysis on a ticket system. I would like to display in one column the total amount of tickets issued on that day and on the corresponding column cell the amount of tickets that failed.
i thought I would be able to do this with a pivot table but it is proving more challenging than I thought.
I have attached the table I am using.
On the row labels, I use the due date.I put in 2 fields in the value section. In column B I would like to see the failed tickets (i.e. the disposition field where I filter out 'not implemented' 'rejected' and 'failed'). In column C I would like to see the total amount of tickets.
The problem I am having is that whenever I out in the filter it filters out for the values no matter what. I tried to see if I can set a filter for a field in the values section but it does not appear that I can.
I hope I explained this properly. If anyone can ssist I would be very grateful.
Thanks,
Nathan

**teylyn** · 03-20-2010, 10:31 PM

Hi,

Get rid of your report filter. Create a Calculated Item for Disposition, call it Unsuccessful, and give it the formula

=Failure +'Not Implemented' +Rejected

Then pull Disposition in the Column Labels and put another field, for example Status into the Values.

Filter the Disposition field to show only "Unsuccessful", Change authorised, Successful and Successful with modifications.

Now you see them side by side. See attached. You may want to include more stati in the Unssuccessful item. I wasn't quite sure what to do with Change authorised and Successful with modifications, so I left them out.

see attached.

**nmss18** · 03-22-2010, 12:49 PM

Teylyn,
This is excellent.Exactly what I was looking for. Thanks. However, this is my first introduction to calculated items and was having difficulty following the logic. I did not find the excel help much help here and was wondering if you could provide some more detail on how you did this or alternatively a good link on the subject. Much appreciated.
Nathan

**teylyn** · 03-22-2010, 07:06 PM

Hi Nathan, I'm on a learning curve with pivot tables myself and find the lack of documentation frustrating.

Here are some links I found useful
http://blogs.msdn.com/excel/archive/...ted-items.aspx
http://guj800.wordpress.com/2008/06/...e-frustration/