PivotTable - show top items

  • Thread starter Thread starter Epinn
  • Start date Start date
E

Epinn

Re: http://www.contextures.com/xlPivot05.html#TopItems

I use the data file from the above site.

I drag "name" to the row fields area and "score" to the data items area. Then I double-click on name and select top 5 scores. I can even sort in ascending order of scores. No problem.

However, if I just drag score to the row fields area and nothing else, when I click on advanced, "Top 10 AutoShow" is greyed out. The way to get around this is to also drag score to the data area i.e. two identical columns of score side by side. Then I can choose the top items.

My interpretation is that we can only show top items on data fields and they must be in the data area.

Any comments?

Epinn
 
Epinn

In order for anything to be ranked, it must have a criterion - Number,
Value, Percentage or whatever and those fields recording that
information have to be in the data area if they are to be used for the
purpose of ranking.

Any Field in the row area can be ranked according to the criterion.
Sales Person by value of sales, Category by value of sales, Region by
value of sales etc.

One would not normally drag Sales to the row area of a report, but you
can if you want to.
It could be sorted, which would have the same effect as ranking, and you
could hide all but the first 10 items in the list.
Alternatively, you could also repeat Sales as a data item and choose Top
10

--
Regards

Roger Govier


Re: http://www.contextures.com/xlPivot05.html#TopItems

I use the data file from the above site.

I drag "name" to the row fields area and "score" to the data items area.
Then I double-click on name and select top 5 scores. I can even sort in
ascending order of scores. No problem.

However, if I just drag score to the row fields area and nothing else,
when I click on advanced, "Top 10 AutoShow" is greyed out. The way to
get around this is to also drag score to the data area i.e. two
identical columns of score side by side. Then I can choose the top
items.

My interpretation is that we can only show top items on data fields and
they must be in the data area.

Any comments?

Epinn
 
Roger,

Thanks for the info.
can if you want to. <<

I will only do this in an experiment. I have two columns - Product Name and Qty. Looks fine. If I just drag qty to the row area and nothing else, I lose a couple of records. Scary!

Epinn

Epinn

In order for anything to be ranked, it must have a criterion - Number,
Value, Percentage or whatever and those fields recording that
information have to be in the data area if they are to be used for the
purpose of ranking.

Any Field in the row area can be ranked according to the criterion.
Sales Person by value of sales, Category by value of sales, Region by
value of sales etc.

One would not normally drag Sales to the row area of a report, but you
can if you want to.
It could be sorted, which would have the same effect as ranking, and you
could hide all but the first 10 items in the list.
Alternatively, you could also repeat Sales as a data item and choose Top
10

--
Regards

Roger Govier


Re: http://www.contextures.com/xlPivot05.html#TopItems

I use the data file from the above site.

I drag "name" to the row fields area and "score" to the data items area.
Then I double-click on name and select top 5 scores. I can even sort in
ascending order of scores. No problem.

However, if I just drag score to the row fields area and nothing else,
when I click on advanced, "Top 10 AutoShow" is greyed out. The way to
get around this is to also drag score to the data area i.e. two
identical columns of score side by side. Then I can choose the top
items.

My interpretation is that we can only show top items on data fields and
they must be in the data area.

Any comments?

Epinn
 
Epinn
If I just drag qty to the row area and nothing else, I lose a couple of
records. Scary!
Click to expand...
I'm willing to bet that you don't <vbg>

Either there are duplicate numbers in your Qty, and dragging it to the
row area will (quite correctly) show the value only once
or
Clicking the dropdown on Qty, Show All isn't selected

--
Regards

Roger Govier


Roger,

Thanks for the info.
can if you want to. <<

I will only do this in an experiment. I have two columns - Product Name
and Qty. Looks fine. If I just drag qty to the row area and nothing
else, I lose a couple of records. Scary!

Epinn

Epinn

In order for anything to be ranked, it must have a criterion - Number,
Value, Percentage or whatever and those fields recording that
information have to be in the data area if they are to be used for the
purpose of ranking.

Any Field in the row area can be ranked according to the criterion.
Sales Person by value of sales, Category by value of sales, Region by
value of sales etc.

One would not normally drag Sales to the row area of a report, but you
can if you want to.
It could be sorted, which would have the same effect as ranking, and you
could hide all but the first 10 items in the list.
Alternatively, you could also repeat Sales as a data item and choose Top
10

--
Regards

Roger Govier


Re: http://www.contextures.com/xlPivot05.html#TopItems

I use the data file from the above site.

I drag "name" to the row fields area and "score" to the data items area.
Then I double-click on name and select top 5 scores. I can even sort in
ascending order of scores. No problem.

However, if I just drag score to the row fields area and nothing else,
when I click on advanced, "Top 10 AutoShow" is greyed out. The way to
get around this is to also drag score to the data area i.e. two
identical columns of score side by side. Then I can choose the top
items.

My interpretation is that we can only show top items on data fields and
they must be in the data area.

Any comments?

Epinn
 
I'm willing to bet that you don't <vbg>

I was going to say how much $$$ but then I don't want to turn this forum into online gaming. ;)
row area will (quite correctly) show the value only once.

Yes, I have covered that already, but still ...... There has to be a reason and I haven't figured it out yet.

I haven't told you that the PT was based on *two* sheets with identical column headings. I have taken two sheets into consideration as well. But that shouldn't make a difference between dropping the field into data area vs. row area.

If I still haven't found the reason later today and if you want to see my PT, I can e-mail it to you and I know your e-mail address.

Epinn

If I just drag qty to the row area and nothing else, I lose a couple of
records. Scary!
Click to expand...
I'm willing to bet that you don't <vbg>

Either there are duplicate numbers in your Qty, and dragging it to the
row area will (quite correctly) show the value only once
or
Clicking the dropdown on Qty, Show All isn't selected

--
Regards

Roger Govier


Roger,

Thanks for the info.
can if you want to. <<

I will only do this in an experiment. I have two columns - Product Name
and Qty. Looks fine. If I just drag qty to the row area and nothing
else, I lose a couple of records. Scary!

Epinn

Epinn

In order for anything to be ranked, it must have a criterion - Number,
Value, Percentage or whatever and those fields recording that
information have to be in the data area if they are to be used for the
purpose of ranking.

Any Field in the row area can be ranked according to the criterion.
Sales Person by value of sales, Category by value of sales, Region by
value of sales etc.

One would not normally drag Sales to the row area of a report, but you
can if you want to.
It could be sorted, which would have the same effect as ranking, and you
could hide all but the first 10 items in the list.
Alternatively, you could also repeat Sales as a data item and choose Top
10

--
Regards

Roger Govier


Re: http://www.contextures.com/xlPivot05.html#TopItems

I use the data file from the above site.

I drag "name" to the row fields area and "score" to the data items area.
Then I double-click on name and select top 5 scores. I can even sort in
ascending order of scores. No problem.

However, if I just drag score to the row fields area and nothing else,
when I click on advanced, "Top 10 AutoShow" is greyed out. The way to
get around this is to also drag score to the data area i.e. two
identical columns of score side by side. Then I can choose the top
items.

My interpretation is that we can only show top items on data fields and
they must be in the data area.

Any comments?

Epinn
 
Roger,

I have started a new thread in the Excel functions Newsgroup. Subject is PivotTable ...... Date is today.

I don't think you want to bet with me. ;)

Epinn

If I just drag qty to the row area and nothing else, I lose a couple of
records. Scary!
Click to expand...
I'm willing to bet that you don't <vbg>

Either there are duplicate numbers in your Qty, and dragging it to the
row area will (quite correctly) show the value only once
or
Clicking the dropdown on Qty, Show All isn't selected

--
Regards

Roger Govier


Roger,

Thanks for the info.
can if you want to. <<

I will only do this in an experiment. I have two columns - Product Name
and Qty. Looks fine. If I just drag qty to the row area and nothing
else, I lose a couple of records. Scary!

Epinn

Epinn

In order for anything to be ranked, it must have a criterion - Number,
Value, Percentage or whatever and those fields recording that
information have to be in the data area if they are to be used for the
purpose of ranking.

Any Field in the row area can be ranked according to the criterion.
Sales Person by value of sales, Category by value of sales, Region by
value of sales etc.

One would not normally drag Sales to the row area of a report, but you
can if you want to.
It could be sorted, which would have the same effect as ranking, and you
could hide all but the first 10 items in the list.
Alternatively, you could also repeat Sales as a data item and choose Top
10

--
Regards

Roger Govier


Re: http://www.contextures.com/xlPivot05.html#TopItems

I use the data file from the above site.

I drag "name" to the row fields area and "score" to the data items area.
Then I double-click on name and select top 5 scores. I can even sort in
ascending order of scores. No problem.

However, if I just drag score to the row fields area and nothing else,
when I click on advanced, "Top 10 AutoShow" is greyed out. The way to
get around this is to also drag score to the data area i.e. two
identical columns of score side by side. Then I can choose the top
items.

My interpretation is that we can only show top items on data fields and
they must be in the data area.

Any comments?

Epinn
 
Back
Top