Counting Instances in a Column

zellingj

Hi.  I was given a very useful formula in Excel that I am trying to replicate in OBIEE.  In short, it counts (attaches the value 1) to every discrete instance in a column.  In the case I'm working on, I have a long list of dates and countries.  I need to isolate the countries as instances - so in 2010 we had 48 different countries represented, in 2011 we had 56, etc.  The base table has thousands of rows so I only need to attach a 1 to the first instance of "Date-Country" and leave the remaining identical iterations as 0.  Here is the code I used in Excel. 

=IF(COUNTIF(M$2:$M2,M2)=1,1,0)

In the Execl the second and third M2 would follow the column to it's end (M3, M4, etc.) searching for matches.

Any help would be appreciated.  Thank you.

Gianni Ceresa

Hi,

What about a COUNT DISTINCT ?

It count the unique values ignoring duplicates etc.

It is one of the standard aggregation available in OBIEE.

Thomas Dodds

"I was given a very useful formula in Excel that I am trying to replicate in OBIEE."

Replicating formulas is always a goose chase ... replicate intent ... the intent is to have distinct count - then you have basis for which to inspect the developer guide to find as @Gianni Ceresa has indicated

zellingj

Thanks!  I think I sort of figured it out with a combination of Count Distinct and calculated fields.