complex Count statement

[marko_rakar]

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?

[tresesco]

Try like:

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

[MK_QSL]

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

[marko_rakar]

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?