[QS] Dynamic Count Distinct

[jiunshen]

Guys,

I used count(distinct customer_id) to provide the number of customer. Since we need to do some filters to group different customers, I creates a data item in Framework Manager only contains customer_id and change the Usage as Fact and Regular Aggregate as Count Distinct.

It works fine mostly, but sometimes in Query Studio, the footer summary value is a sum of the rows in the report, not a summary count distinct of the full underlying dataset.

I found an article in KB (Document# 1025531.1), but it seems not the one I want. Does anyone have this kind of problem before?

Thanks for your answer

[jiunshen]

Guys,

I have solved this problem.  ;D

For your information, if you want to create a query item which represents the Count(Distinct xx) and have to be sum up as the default aggregation. (my example is Count(Distinct Customer_ID))

1. You have to create another "fact" query item first (no matter what's the data is, just make sure it must be a fact item.)

2. Change the Calculation Definition to the xx from data source view you want to count to (my example is Customer_ID)

3. Change the Regular Aggregate in Properties in Project Viewer as Count Distinct

4. Keep the Semi-Aggregate in Properties in Project Viewer as Sum

Then Done!

Enjoy :)

[blom0344]

Sorry to reopen an old post, but in your opinion what is the proper way to solve a regular count of an certain ID?

From my BO background I am used to define an expression as:

Count(ID)       [ A regular SQL expression]

Cognos best practices advices to solve it the other way around:

1. Create new query item
2. Define expression as :  1
3. Set usage to fact
4. Set aggr. behavior to sum

A problem may arise with nullable fields.
The  SQL count function skips nulls while performing a count, whereas the Cognos best practice solution
would simply perform a record count and yield a higher outcome

This came up today in a discussion how to build facts on factless facttables  where the only facts are counts of certain fields