Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

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.

Tags (3)
1 Solution

Accepted Solutions
sebasdpereira
New Contributor II

Re: Count Distinct, Multiple Measures (Sum)?

Hi, try


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

11 Replies
MVP
MVP

Re: Count Distinct, Multiple Measures (Sum)?

How exactly did you add Product_2 and Product_3?

Not applicable

Re: Count Distinct, Multiple Measures (Sum)?

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.

MVP
MVP

Re: Count Distinct, Multiple Measures (Sum)?

May be try this:

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

Not applicable

Re: Count Distinct, Multiple Measures (Sum)?

Hi Sunny,


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

MVP
MVP

Re: Count Distinct, Multiple Measures (Sum)?

Is ID and Product coming from different tables?

Not applicable

Re: Count Distinct, Multiple Measures (Sum)?

Hi Sunny,


No these are all on the same table.

MVP
MVP

Re: Count Distinct, Multiple Measures (Sum)?

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
New Contributor II

Re: Count Distinct, Multiple Measures (Sum)?

Hi, try


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

sebastiandperei
Valued Contributor

Re: Count Distinct, Multiple Measures (Sum)?

If you want to use Set Analysis use

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