Conditional count

[V i n c e n t]

Hello all,

I need you help to for my problem.

My data : (for example)

CUSTOMERID  YEAR REVENUE
12310            2009   100
15666            2009   150
15666            2010   350
14899            2010   200
12324            2010   140
33467            2009   690
33467            2010   450
23423            2009    330
56464            2010    110

I need to count 3 things :
customer with revenue in 2009 but no revenue in 2010 (the result from example = 2)
customer with no revunue in 2009 but with revenue in 2010 (= 3)
customer with revenue in both year (= 2)

It's very hard to do that because it's like compare a null data with no null data.
I tried with count, count(distinct).. with no success.

Can someone help me ?

Thanks.

[V i n c e n t]

No one to help me  ??

[CognosPaul]

Excuse the brevity, not much time today.

Create a new query, drag in Customer ID.

Create a new data item called 2009 Revenue:
case when [NS].[Time].[Year] = 2009 then [NS].[Measures].[Revenue] else 0 end

Create a new data item called 2010 Revenue:
case when [NS].[Time].[Year] = 2010 then [NS].[Measures].[Revenue] else 0 end

Create a new data item called CountType1:
case when [2009 Revenue] > 0 and [2010 Revenue] = 0 then 1 else 0 end

Create a new data item called CountType2:
case when [2009 Revenue] = 0 and [2010 Revenue] = > then 1 else 0 end

Create a new data item called CountType3:
case when [2009 Revenue] > 0 and [2010 Revenue] > 0 then 1 else 0 end

Set the aggregations on everything but CustomerID to total.

Create a list based on this query with only the CountTypes.

[V i n c e n t]

Thanks for your solution, i will try this.

[V i n c e n t]

I try this but don't work.

Can you explain why i need customer ID if i dont use it in the list and in the data item ?
I need more explanations for the aggregation function.

Thanks.