Count Distinct, Multiple Measures (Sum)?

[#1]

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.

[sunny_talwar]

How exactly did you add Product_2 and Product_3?

[#3]

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)

[#5]

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?

[#7]

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 · Accepted solution]

Hi, try

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

[sebastiandperei]

If you want to use Set Analysis use

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

• Count Distinct, Multiple Measures (Sum)?