Count function

[#1]

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

[IAMDV]

Hi,

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

Cheers - DV

[#3]

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?

[IAMDV]

Hi,

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

I'll get back to you soon.

Cheers - DV

[IAMDV]

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

[#6]

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

[IAMDV · Accepted solution]

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

[Gabriel]

Hi DV,

This is indepth explanation. Well done.

[#9]

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