Distinct Count - case statement?

[ksbellow]

I'm trying to do a distinct count on an identifying data object based on the value of another data object.

So far I have this:

case
when [Query1].[CATEGORY_CD] = 'AST' then (count (distinct [Query1].[Id_Num]))
end

I get inflated counts when I use this.  I've also tried removing the distinct, and changing aggregate Function from Automatic to Count Distinct, but then I get back only one's and zero's.

There are 10 different conditions that go with the CATEGORY_CD that I plan on using this code to get the different counts for.

Any help would be appreciated.

[Lynn]

Code Select Expand


count ( distinct [Query1].[Id_Num] for [Query1].[CATEGORY_CD] )


[ksbellow]

Unfortunately that code won't give me the data in the format I want.  I want one column for each condition.  IE

Condition 1  Condition 2  Condition 3
16                23                25

Instead of
Condition 1 - 16
Condition 2 - 23
Condition 3 - 25

The best way I could figure to do it would be to have each condition be a data item itself, that I can then put into a listing report.

[Lynn]

Would a crosstab layout be worth a try to allow you to get the format you describe?

Otherwise I think you're on the right track by having a data item for each value. So your data item for AST would contain the expression something like below and you'd set the aggregate function to count distinct

Code Select Expand


case
when [Query1].[CATEGORY_CD] = 'AST' then 'AST'
else null
end


You'd have as many of these as you have conditions and this, of course, presumes that the list of conditions is static so that a new one won't require report revisions. A crosstab avoids that problem as it would string out the conditions as columns for as many as there are.

[ksbellow]

I'm not using the crosstab feature as the number of metrics that are going in the report and the format it is requested in don't work together.  I have several dozen metrics, most of which are cut up by the different conditions (IE an overall total for Metric 1, then total for Metric 1 with condition 1, total for Metric one with Condition 2, etc).  Like below:

Metric 1 - Overall Total  Metric 1 Condition 1  Metric 1 Condition 2
100                               15                              34
 
Metric 2 - Overall Total  Metric 2 Condition 1  Metric 2 Condition 2
234                               20                              39
 
(Except all these titles rows would be in the same row in Excel, Row 1.) 

The report will be spitting out these totals by account and other options, and the requester specifically asked that the cells that identify the account info not be merged (when you drop the data into a crosstab, it automatically spits out the columns as merged cells, if there are several rows that have the same account num/name etc.)  The only way I could figure to do this was in a list format.

I'm trying to figure out how to use that code above to get a distinct count on the ID_Num.  When I hit the count distinct, I get 1 or 0, as I guess it's counting the existence of AST and not the total number of unique ID's attached to that descriptor.

[Lynn]

oh duh!! I'm sorry... :-[

Yes, what I gave you just would count distinctly the category value. Maybe this works:

Code Select Expand


casewhen [Query1].[CATEGORY_CD] = 'AST' then [ID Value] else null end


and set that to count distinct aggregate funciton...

[ksbellow]

Looks like this did it. Amazing. Thanks so much.

[ksbellow]

Another issue with this -

I have my query set up that calculates my totals for my ten conditions.  I then join it to another query, so I can display the totals from this query in same report as totals calculated in a different query.  I join the two queries on the account info - account name, num, etc.  In the final query, my condition totals are showing a value of "1".  If I run the condition query on it's own, it works fine, but the data is not crossing over through the join into the final query.  Any ideas on how to fix this?

I'm using a 0...n cardinality on both queries in case some accts don't have totals for all metrics.

[Lynn]

Try playing with the aggregate settings on the final query. It is probably "Automatic" by default, so maybe Calculated is what you want. Not exactly sure but usually I hack around and get it...

[melee]

Try playing with the aggregate settings on the final query. It is probably "Automatic" by default, so maybe Calculated is what you want. Not exactly sure but usually I hack around and get it...

Lynn and I actually had a long bout with this before figuring out that "None" as an aggregate type fixes this in most cases.

[ksbellow]

Calculated did the trick.

[Lynn]

I wasn't sure in this case. "None" works for me when the expression in the original query contains the explicit summary function such as

total ( [BlahBlahBlah] for [Whatever], [Nonsense] )

but if the expression is just a query item and the aggregate property for that item is what drives the correct value (count distinct in this case) then the aggregate function in the subsequent query (referenced or joined) isn't necessarily what I expect it would be at the outset!  :o