9 Replies Latest reply: May 14, 2012 6:41 PM by Jorge Villalobos

# calculate with count

Hello ,

i have a simple straight table with dimension customer and a calculated category (i can't use the category in dimension):

customer     category

1                    A

2                    A

3                    B

4                    C

5                    C

i want smiply count the category for example category A = 2 but it doesn't work with count(category)..

any idea?

• ###### calculate with count

Hi,

if your dimension is customer then you count(category) your count will always be = 1^^

because expression will base its grouping from your dimension. and since your dimension (customer) has a unique data then your count will always be 1.

customer     category     count(category)

1                    A                        1

2                    A                        1

3                    B                        1

4                    C                        1

5                    C                        1

Regards,

Alex

• ###### Re: calculate with count

yes i know but how i can sum for example the A category so that i have :

category     value

A               2

B               1

C               2

i can't use the calculated category as a dimension because its not a field or a simple if statement

• ###### Re: calculate with count

Hi,

You can use aggr : aggr(count(customer),category)

Regards,

Vincent

• ###### Re: calculate with count

good idea but it not works ..i think because the aggr function needs an dimension ..

• ###### Re: calculate with count

Sorry, I have not seen that your dimension category is calculated.

• ###### calculate with count

Unfortunately not because if i calculate the category in script, the category will not calculate dynamic on the layout because then the category is satic for example i select another year ..

• ###### Re: calculate with count

see the attched file

hope this helps

• ###### Re: calculate with count

Hi there,

If Sunil's idea is not working for you, (you say you can't use category as a dimension) you could try to add a counter in your script which create a counted total in one field in your script.

I added Sunil's QV-document to clearify what I mean:

t:

* ;

customer   ,  category

1          ,  A

2              ,      A

3             ,       B

4             ,       C

5             ,       C

];

Join (t)

Sum(counter) as CountCategory

Resident t

Group by category;

drop field counter from t;

This will add a information field CountCategory which you can use (dont use it as a sum() )

• ###### Re: calculate with count

category as category2

from ...

Apply any transformation from the data of that field that may benefit your application performance.

count the duplicated script

count(category2)