topic Re: complex Count statement in QlikView

complex Count statement

marko_rakar — Thu, 19 Mar 2015 07:52:49 GMT

I have a chart (table) where I am trying to do simulation on how many subjects will be affected by reaching particular goal.

This works correctly if I have single parameter:

count({<Total={'<=$(vGroup1)'}>} customer_ID)

I am trying to count unique customer_ID instances where Total is equal or less number found in my variable vGroup1

Now, the thing is that the total amount is basically a sum of 10 different groups of numbers (think of different sales divisions within single customer_ID, and also it is possible that one customer_ID and one sales division have more transactions), something like:

customer_ID, division, total

customer_1 division1 1000

customer_1 division1 500

customer_1 division2 700

customer_1 division3 100

customer_1 division3 2000

customer_2 division1 50

customer_2 division1 150

customer_2 division2 300

customer_2 division3 400

customer_2 division3 800

and I want to have more then one variable involved, what I am trying to do is to do following:

Count all unique customer_ID whose sum of Total for the division1 is equal or less then vXxxx AND sum of Total for the division2 is equal or less then vYyyyy AND sum of Total for the division3 is equal or less then vZzzzz...

any ideas on how to make it happen?

Re: complex Count statement

tresB — Thu, 19 Mar 2015 07:57:26 GMT

Try like:

count({<customer_ID={"=Sum(Total)<=$(vGroup1)"}>} customer_ID)

Re: complex Count statement

MK_QSL — Thu, 19 Mar 2015 07:58:11 GMT

Can you provide few lines of sample data along with the result you are looking for?

Re: complex Count statement

marko_rakar — Thu, 19 Mar 2015 08:36:38 GMT

Please find attached sample file with sample data.In the chart "Sum of all" in the most right collumn I have Complex count I wish to make.

Currently, in the saved file, my variable "Division 1 target" is set to 18 and in my Complex count it states that there are four "hits".

This is incorrect, because there are four rows in my database which meet the criteria but there are only three distinct customers which are selected.

So my first problem is to count correctly distinct customer_ID which is collumn F1 (this is not working at the moment, I just noticed that).

My second problem is that I want to introduce another variable vDivision2 so that my complex count will count all customers which have met criteria with their division 1 and division 2, for example:

count(DISTINCT F1 where

sum of all rows F3 is equal or less then vDivision1 if field F2 is equal to 1 AND

sum of all rows F3 is equal or less then vDivision2 id field F2 is equal to 2

Now, if I do this correctly and if I set slide Division 1 to the value of 65 (customers 1 and 4 meet this) and vDivision 2 to the value of 100 (customers 1 and 2 meet this) then correct number in my complex count should be 1 because only one customer meets both criteria (customer1). But, if I increase value in Division1 to 100, then correct answer is 2 because customer1 and customer2 meet criteria.

Can it be done?