
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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:
- count
- set analysis
- sum
- « Previous Replies
-
- 1
- 2
- Next Replies »
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi, try
COUNT ({< ID=P({<Product_1={">0"}>}) + P({<Product_2={">0"}>}) + P({<Product_3={">0"}>}) >} DISTINCT ID)

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
How exactly did you add Product_2 and Product_3?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
May be try this:
Count({$<ID = {"=PRODUCT_1 > 0 and PRODUCT_2 > 0 and PRODUCT_3 > 0"}>} DISTINCT ID)

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Sunny,
I have tried this already. Sadly, it's not showing the correct figure

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Is ID and Product coming from different tables?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Sunny,
No these are all on the same table.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi, try
COUNT ({< ID=P({<Product_1={">0"}>}) + P({<Product_2={">0"}>}) + P({<Product_3={">0"}>}) >} DISTINCT ID)


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
If you want to use Set Analysis use
COUNT ({< ID=P({<Product_1={">0"}>}) + P({<Product_2={">0"}>}) + P({<Product_3={">0"}>}) >} DISTINCT ID)

- « Previous Replies
-
- 1
- 2
- Next Replies »