Custom column in pivot table

[Mandark]

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?

 

[Mandark]

UP!

 

[Fazza]

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

 

[Mandark]

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.

 

[Kyle123]

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?

 

[Mandark]

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.

 

[Kyle123]

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.

 

[Mandark]

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)?

 

[Kyle123]

Not as far as I know

 

[Kyle123]

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