Can I show the difference between 2 counted columns in a pivot?

I have data that has rows of tickets. Each row has a year, a created date and a closed date.

I am trying to create a pivot that shows the number of tickets opened in a year and closed in a year. This is done by just doing a count on the created date and closed date.

What I would like to do is in the 4th column of the pivot, just simply subtract the count of created column with from the count of closed column. I can't figure it out though. I could do this calculation next to the pivot and off to the side but Id rather it be contained in the pivot.

Thanks

Originally Posted by dflak

There is a subtle "feature" of pivot tables: you can't do calculation on counts. While a count looks like it is a number, the pivot table can't handle it. Pivot tables work on a record-by-record basis and asking it to subtract one string from another or a number from a string or vice versa, just isn't going to work.

The work-around is to resort to a helper column. Sometimes the formula is as simple as =1 as in the case where you might be doing a count of names. Every record has exactly one name. Other times it may be a bit more complicated such as =IF(ISBLANK(Closed Date),0,1).

The helper columns "convert" the count into a value and values can be computed.

Without seeing the source data, this is as far as I can take it.

If I put in a helper column and had it be 1 whenever there was a date, would you then sum this in the pivot, taking the place of the count?

If I have that correct, is there a way to get that subtraction calculation in the pivot?

This thread just helped me tremendously! Thank you