topic Count function in QlikView

Count function

Wed, 07 Dec 2011 12:56:12 GMT

Hello,

I have the following data regarding Buyback transactions:

Product

Total Sales

Date of Sale

Region

etc...

I want to:

1. Somehow show the amount of times (count) a product appeared for a Region, but I dont know how to formulate it

2. Assistance with a chart - advice on which one would be most effective, and some help on how to do it

ANY help will be appreciated. Only been using the product for a week!

Regards

Count function

IAMDV — Wed, 07 Dec 2011 14:27:57 GMT

Hi,

Please select Product as Dimension and Count(Product) as expression. This should work.

Cheers - DV

Count function

Wed, 07 Dec 2011 14:37:34 GMT

Thank you D V

Can I count by multiple columns? i e I want to count the Product Sold per Customer

Something like count (product, customer)? I assume customer would be added to the DImension list?

Count function

IAMDV — Wed, 07 Dec 2011 14:40:30 GMT

Hi,

You have to use Aggr() function. I'll create an example for you.

I'll get back to you soon.

Cheers - DV

Re: Count function

IAMDV — Wed, 07 Dec 2011 14:57:50 GMT

Oops! I misunderstood on what you said. Sorry about that.

I am not sure on why you wanted to count multiple columns. I am attaching a simple example. If this not what you want then please let me know the expected output from this example and that makes it easy for me to understand.

Good luck!

Cheers - DV

Count function

Wed, 07 Dec 2011 15:22:22 GMT

D V, thank you for your answer...

On your example - you only have 3 fields to work with. What if you had many more, lets say Adress etc etc. Your Expression will stay the same (Count(Customer)) I assume, but now that all those other fields have been added, surely the answer will change?

I come from a traditional SQL background. For me:

select count(Customer) is not the same as

select count(Customer, product) GROUP BY Product

The output is way different.

Is that the case in Qview as well?

If so - how do you limit the fields that you select? You might have lots of other sheets bringing in other data and it will affect the outcome

Re: Count function

IAMDV — Wed, 07 Dec 2011 15:58:06 GMT

Hi Again,

Now I understand your question (hopefully!). Even I come from MS SQL Server background. I have worked as SQL Developer and totally understand where you are coming from...

Even if we load additional fields your answer will not change as long the Customer field does not change. QlikView works based on associative logic, it is little similar as Set based approach but not same as SQL. So let’s say we have Address & other fields loaded, if these fields are part of same table then nothing changes. So your expressions are still valid.

However, if these new fields are from different table and if they are linked with Customer Field (We call this as Key Field in QlikView). And now if the number rows in each is different... for example you have two tables (T1 and T2). If these tables are linked with Key Field (Customer) in our case, now if you perform aggregation on the Customer field then you will get unexpected results. So it is not recommended to use the Key Field to perform aggregations.

Also, we have Aggr() function samilar to Group By clause in SQL.

I hope this makes sense and I haven’t confused you.

I am also writing some explanation on QlikView Architecture works when we load the data...

The data records are read into the memory, so that all the processing of data may be done through memory. I am sure you know this bit. QlikView treats all the data as Data Element Type (Columns / Fields) and Data Element Values (Values / Records). So each different data element value of each data element type is assigned a binary code and the data records are stored in binary-coded form and they are also sorted. By using the binary coding, very quick searches can be done on the tables. Also, QlikView removes the redundant information information and reduces the amount of data. However, the redundant information in stored as seperately with the frequencies for each unique data element value and across each data element type. When user makes a selection on data element values then the implied selection (possible values) are kept track seperately to present them to the user. By this process QlikView can perform rapid linear searches.

I understand this is not in detail but just a quick overview. I hope this helps!

Good luck!

Cheers - DV

Re: Count function

Gabriel — Wed, 07 Dec 2011 16:28:32 GMT

Hi DV,

This is indepth explanation. Well done.

Re: Count function

Wed, 07 Dec 2011 18:46:12 GMT

Thank you so much D V. Excellent and very helpful answer