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 Distinct, Multiple Measures (Sum)?

Hi,


I have three products which I am trying to sum on a distinct ID number.

My current set analysis is:


COUNT ({$< Product_1 = {">0"}>}  DISTINCT ID)

There are two other products Product_2 & Product_3.

I have tried adding them together using comma separators, however this prints out the wrong number compared what is printed out trough my SQL code.

Help would be appreciated.

1 Solution

Accepted Solutions
sebasdpereira
Partner - Contributor III
Partner - Contributor III

Hi, try


COUNT ({< ID=P({<Product_1={">0"}>}) + P({<Product_2={">0"}>}) + P({<Product_3={">0"}>}) >}  DISTINCT ID)

View solution in original post

11 Replies
sunny_talwar

How exactly did you add Product_2 and Product_3?

Not applicable
Author

In SQL I added them like so:

select count(distinct ID) as Number

from table1 x

where ( X.Product_1 + X.Product_2 + X.Product_3 ) > 0 and

X.Year = '2016/05'

However, in set analysis I am not too sure how.

I added them like:

Count({$<PRODUCT_1+={">0"},PRODUCT_2+={">$(>0)"},PRODUCT_3+={">$(>0)"}>} DISTINCT ID)


Which is wrong. As all three need to be greater than 0 to be distinct.

This IF statement works accordingly, however set analysis will be needed due to the volume of the data:

count (DISTINCT if((PRODUCT_1+ PRODUCT_2 + PRODUCT_2)>0, ID))

Thanks.

sunny_talwar

May be try this:

Count({$<ID = {"=PRODUCT_1 > 0 and PRODUCT_2 > 0 and PRODUCT_3 > 0"}>} DISTINCT ID)

Not applicable
Author

Hi Sunny,


I have tried this already. Sadly, it's not showing the correct figure

sunny_talwar

Is ID and Product coming from different tables?

Not applicable
Author

Hi Sunny,


No these are all on the same table.

sunny_talwar

Would you be able to create a straight table with ID as dimension and the following expression:

Sum(Aggr(If(PRODUCT_1 > 0 and PRODUCT_2 > 0 and PRODUCT_3 > 0, 1, 0), ID))

What do you get for the total is not the correct number you are looking to get?

sebasdpereira
Partner - Contributor III
Partner - Contributor III

Hi, try


COUNT ({< ID=P({<Product_1={">0"}>}) + P({<Product_2={">0"}>}) + P({<Product_3={">0"}>}) >}  DISTINCT ID)

sebastiandperei
Specialist
Specialist

If you want to use Set Analysis use

COUNT ({< ID=P({<Product_1={">0"}>}) + P({<Product_2={">0"}>}) + P({<Product_3={">0"}>}) >}  DISTINCT ID)