Subtracting two pivot table columns based on a count

I'm attempting to create a pivot table to show the count of created tickets, and the count of resolved tickets and the backlog between the two.

I have the pivot table setup right now so that the Rows are the Ticket Queue names, and there are two columns, Count of Created and Count of Resolved, which gives me the total number of tickets created in that queue on a particular month/week/day/whatever, and the total number of tickets resolved in that same time frame.

The Created and Resolved fields are dates in my datasheet. The pivot table currently looks like this:

Please Login or Register  to view this content.

I would like to add a column for "Backlog" which would simply give me the difference in the count. Currently if i add a calculated field and use the formula =Created-Resolved, i get very large or negative numbers, i'm assuming this is because the field is a date field and it's actually subtracting the time between the two columns rather than the count.

I'd like to see my table like this:

Please Login or Register  to view this content.

I could then add in other data such as month and see the backlog of tickets from month to month. I'd also like this data to be easily updatable, because this information will be processed weekly, monthly and sometimes daily so if i could create the table to display the info i can just update the datasheet and refresh the pivot table to display the new information.


Any help would be greatly appreciated.

Thank you,
Tyler

Hello Tyler, welcome to the fourm. How about sharing a sample PT.
Best Regards/VKS

Sure thing, here you go.

Demo_PivotTable.xlsx

As i go through your demo file, please check the attached file that i worked on while waiting for the sample file.
Please do let me know if this is what you want.
Best Regards/VKS

Ummmm, that's sort of what I'd like, perhaps the demo file i attached will help illustrate what i'm looking for.

Enjoyed doing it. Attached, is the pivot table the way you would want to see it. Its bit too late here keeping in mind working day tomorrow. Send me your questions if you have any and i will be happy to try and answer them.
Best Regards/VKS

Hello everyone.

As far as I see in the attached file, final calculation is done based on additional pivot. Something like: Data -> Pivot 1 to calculate Counted values -> pivot 2 to calculate subtraction between Counted values.

But is there any way to add this subtracting int the pivot 2. So it will contain something like "= count of value1 - count of value 2"

Hi,
this is the exact problem i have in that i have a sum of count in the column of one row and a another count sum of count in another column and i wish to subtract one from the other.
VKS has shown the answer in the form of a pivot. but how was it done so i can apply it to my pivot table.
any help is very much appreciated.
thanks

Hi tsadams23,

I believe that it is better to rearrange the input data. Make Date as a field. More analysis would be possible by doing this.

Thanks

Attachment 631276