Counting DISTINCT value

general discussion
1

Hi,
I used a case statement in the universe to count the ‘distinct’ Sale IDs and it works fine in WEBI. I am using XI3.1. However, when I bring in the productcode (with same Sale IDs, but different products), my count changes and is much higher, since it is counting everything. How can I get this to count only once, ie DISTINCT values?

For example:
prod A -ID 1
prod B- ID 1
proc C- ID 2
prod D -ID 2
prod E- ID 3.

If I dont bring in productcode, I get 3 for my count. As soon as pull the productcode into the query, I get 5.

Thank you in advance for your help!

samistl (BOB member since 2012-12-26)

2

Use COUNT DISTINCT (Sales_ID) and set the projection for the measure to database delegated in the universe!

Andreas :de: (BOB member since 2002-06-20)

3

Thank you Andreas!
That worked like a charm…but I am not able to do any other counts based on that measure.

For example, I need to take Sales ID measure (delegated measure) and add where product=A. and it gives me the #TOREFRESH error. When I click on Refresh, I am not getting any values. Is there any way I can do the subcounts? Thank you again.

samistl (BOB member since 2012-12-26)

4

You are encountering the drawbacks of database delegated measures.
To work around that create those measures at the universe level using e.g.:

COUNT DISTINC (CASE WHEN Product_Code = 'A' THEN Sales_ID END)

As an alternative: Within Webi create local report formulas (as measures) using Webi formula syntax of COUNT DISTINCT (look up the exact syntax in Webi manual, please). Note: For that to work you must bring back the dimension you want to count such as Sales_ID into your report (= result object in query panel).

Andreas :de: (BOB member since 2002-06-20)

5

Thank you again!
But it keeps on getting complex. I have a group of productcode that I need to include for example, A,B and C, and within a certain time period. I have CYWTD, PYWTD etc as contexts.
So, I tried writing the case statement like you said, and it works fine. But when since I need to add multiple productcodes I added, INLIST (“A”; “B”;“C”), and it gives me an error.

Count(distinct (case when dbo.ProductCategoryCode =A and .dbo.SalesDetail.DateSold between CYWTD.StartDate and CYWTD.EndDate then dbo.SalesDetail.SalesID else 0 end))

Id appreciate your feedback.

[Moderator Edit: Added code formatting - Andreas]

samistl (BOB member since 2012-12-26)

6

Please, what is the exact error message?

And as it seems your requirements are getting more and more complex, please do consider hiring an experienced universe designer to assist you maybe for a day or so ; -)

Andreas :de: (BOB member since 2002-06-20)

7

Andreas, I am not sure where I can hire an experienced universe designer? Do you know of anybody who would be able to help me and if so, how much would be the cost? Thank you for all your feedback. Appreciate it much.

samistl (BOB member since 2012-12-26)

8

I have a data set asking a customer how many pets they have for example. Is there a way with one query I can count the distinct values (1,2,3, etc)? Thanks!

±---------±-----+
| Customer | Pets |
±---------±-----+
| 20 | 2 |
| 21 | 3 |
| 22 | 3 |
| 23 | 2 |
| 24 | 4 |
±---------±-----+

What I want is a list saying:

2 had 2 Pets
2 had 3 Pets
1 had 4 Pets

shayanjameel08 (BOB member since 2013-10-21)

9

shayanjameel08,

You want to count the customers and group by the number of pets owned.

SELECT COUNT(Customer), Pets
FROM your_Table
GROUP BY Pets

SteweeJenkins (BOB member since 2013-10-24)