Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Count function

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

1 Solution

Accepted Solutions
IAMDV
Luminary Alumni
Luminary Alumni

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

View solution in original post

8 Replies
IAMDV
Luminary Alumni
Luminary Alumni

Hi,

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

Cheers - DV

Not applicable
Author

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
Luminary Alumni
Luminary Alumni

Hi,

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

I'll get back to you soon.

Cheers - DV

IAMDV
Luminary Alumni
Luminary Alumni

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

Not applicable
Author

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
Luminary Alumni
Luminary Alumni

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
Partner - Specialist III
Partner - Specialist III

Hi DV,

This is indepth explanation. Well done.

Not applicable
Author

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