topic Re: calculate with count in QlikView

calculate with count

Tue, 08 May 2012 07:04:58 GMT

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

Tue, 08 May 2012 07:13:37 GMT

Hi,

In your example

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

SunilChauhan — Tue, 08 May 2012 07:16:40 GMT

see the attched file

hope this helps

Re: calculate with count

Tue, 08 May 2012 07:17:23 GMT

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

vincent_ardiet — Tue, 08 May 2012 07:26:25 GMT

Hi,

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

Regards,

Vincent

Re: calculate with count

Tue, 08 May 2012 07:34:08 GMT

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

Re: calculate with count

vincent_ardiet — Tue, 08 May 2012 07:45:17 GMT

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

And you cannot add this dimension in your script and so in your data model ?

calculate with count

Tue, 08 May 2012 07:50:15 GMT

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

Anonymous — Tue, 08 May 2012 08:07:43 GMT

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:
Load '1' as counter,
          * ;
Load * inline [
customer   , category
1          , A
2              ,      A
3             ,       B
4             ,       C
5             ,       C
];
Join (t)
Load category,
          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

Mon, 14 May 2012 22:41:35 GMT

In your load script create a duplicated field based on your category,

Load

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)