count number of rows per category in pivot table

[#1]

hi guys,

i have a pivot table with this hierarchy

category
   product
     total

i wish a column which shows the number of products for each category. can you redirect me to a thread if my question is a duplicate?

[swuehl]

You probably need to look into using the total qualifier with a field list (check the Help for this), like:

=count(total<category> product)

Hope this helps,

Stefan

[MayilVahanan]

hi,

     Try this, it gives number of product in a category

     =Count(Distinct Product)

Hope it helps

[#4]

hi stefan,

i have tried using the count(total<category> product) but its not good:

here is a sample of what i am trying to achieve:

category	Subcategory	product	total products
A	X	AX1	1
A	X	AX2	1
total products			2
A	Z	AZ	1
total products			1
B	M	4	1
B	M	5	1
total products			2
B	O	6	1
total products			1

can you help me?

[swuehl]

In my expression, category and product must be field names, not e.g. column labels.

Your above table shows what you want to achieve or what you see in real life using my expression?

If that's what you want to achieve, I think you should maybe use mayilvahanan's expression count(distinct product) and add a partial sum in presentation tab for your first dimension.

If this does not help you, could you upload a small sample?

[#6]

my expression is count(distinct product) (label>>"Total")

but when i go to presentation tab> i cannot add partial sum to "Total"

why is that?

[swuehl]

Add the partial sum to your first dimension.

[#8]

please guys can you check the file i just uploaded? my internet is a bit slow..that's why i took so much time..

its not working when i pu the partial sum on the first dimension

[#9]

for example

when i expand BLOC1 i have B1-1 and B1-2 and i have total 264 for each.

but this is false because when i collapse BLOC1 i have 264.

[swuehl]

Sorry, my mistake, you need to add the partial sum for the last dimension, Product.