Custom column in pivot table

Mandark

New Member
Joined
Mar 18, 2014
Messages
43
Hi,

I am using a pivot table to display data. In the pivot table I have countries and under each country couple of names. Then how many training a person led and how big attendance there was.

[TABLE="width: 800"]
<tbody>[TR]
[TD][/TD]
[TD]Completed[/TD]
[TD][/TD]
[TD]Cancelled[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Count of Title[/TD]
[TD]Sum of Attendance[/TD]
[TD]Count of Title[/TD]
[TD]Sum of Attendance[/TD]
[TD]Last Training Date[/TD]
[/TR]
[TR]
[TD]CZE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Karel[/TD]
[TD]10[/TD]
[TD]50[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]1-10-2017[/TD]
[/TR]
[TR]
[TD]Peter[/TD]
[TD]5[/TD]
[TD]54[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]7-10-2017[/TD]
[/TR]
[TR]
[TD]Jean[/TD]
[TD]6[/TD]
[TD]23[/TD]
[TD]5[/TD]
[TD]0[/TD]
[TD]4-10-2017[/TD]
[/TR]
[TR]
[TD]Fanda[/TD]
[TD]5[/TD]
[TD]14[/TD]
[TD]2[/TD]
[TD]0[/TD]
[TD]17-10-2017[/TD]
[/TR]
[TR]
[TD]DEU[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Hanz[/TD]
[TD]2[/TD]
[TD]23[/TD]
[TD]5[/TD]
[TD]0[/TD]
[TD]17-10-2017[/TD]
[/TR]
[TR]
[TD]Franc[/TD]
[TD]5[/TD]
[TD]52[/TD]
[TD]3[/TD]
[TD]0[/TD]
[TD]5-10-2017[/TD]
[/TR]
</tbody>[/TABLE]

I have two questions:

1) In the data source, I created additional column where is displayed the last training date (using max(if...)). That means in the source data for each person, there is always the same "Last training date". So I would like to take this info a put into the pivot table. But anywhere I put it, it's always used as another filter and it makes the table much more complex. I would like to have just a custom column with this info without having to expand the whole table.

2) Same applies for the attendance. If the training has been cancelled, the attendance is always 0. Is there a way to hide the whole column?
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
hello, Mandark

What about if the 'Last Training Date' field is the last row field of the pivot table? (The row fields are the ones on the left.)

So, row fields of Country, Name and then 'Last Training Date'. (May need to turn off any totals for the Name field.)

HTH. regards, Fazza
 
Upvote 0
Hello Fazza,

thank you for your answer. That is a great and working suggestion. However, now the date is displayed below the names and takes up another row and the user will have to expand it. If I had it the way I want (explained in the OP), it would be much cleaner.

Also I am not able to make the pivot table display just the date. The pivot table automatically inserts Years and Quarters and even if I remove those from the field list and leave just the date, the pivot table keeps displaying quarter.
 
Upvote 0
I'm not sure I really understand, why can't you just put the last training date in the data area in your pivot table exactly as in your example?

What does your example not have that you want?
 
Last edited:
Upvote 0
I'm not sure I really understand, why can't you just put the last training date in the data area in your pivot table exactly as in your example?

What does your example not have that you want?

If I put the field into Values, than all the columns will multiply to also contain "Count of latest date". If I put the field into Columns, then I will have milion more columns, one for each date that exists in the field data.

I would like to achieve the same thing I have in the example, but I do not know how.
 
Upvote 0
Put it into the values, change count to average and format as date. Since all the dates are the same, the average will be the actual value.
 
Last edited:
Upvote 0
Put it into the values, change count to average and format as date. Since all the dates are the same, the average will be the actual value.

That works great, but now I have it there three times. Once under Completed, once under Cancelled and once as Total. Is there a way to have it only once (one column)?
 
Upvote 0
Actually, if you can live with it at the beginning, put it into the Rows field and switch to "Classic PivotTable Layout" in PivotTable options. Then remove the Subtotal. I think that will give you what you want
 
Upvote 0

Forum statistics

Threads
1,224,817
Messages
6,181,147
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top