Counting distinct.

[former_member214071]

Good day everyone,

I have data similar to this ilustration.

I'm trying to count the distinct months in the dataset.  I tried creating measures with the following settings a none works when I include dimension table fields in the query.

count(distinct month)  projection sum

count(distinct month)  projection none

count(distinct month)  projection count

count(distinct month)  projection delegated -->  this one display  #torefresh in webi when the object is used on a drill .

For this example the measure should always count 3.   Any idea to get this to work on the universe ?  

Thanks in advance.

[Former Member]

To count distinct values you can write down formula

=count(month;Distinct) which will give you distinct values.

Thanks,

Swapnil

[former_member214071]

throws the following error: [Sybase][ODBC Driver][Sybase IQ]Syntax error near 'month' on line 2.

I'm trying to make this on the information design tool  at universe level   not in the webi.

[Former Member]

Can you please let me know your exact requirement?

Why you need this on universe?

Thanks,

Swapnil

[former_member214071]

swapnil,

I would like to define a measure in the universe that return the count of months in the returning dataset.  In the above sample the object should always return 3,  if the there were rows since dic 2014 then it should return 4.

I know this seems to be done more or less easily on each webi report using contexts, but I would like to setup at universer level.

So I can use that object consistently in any webi report.

Thanks

[Former Member]

hi Emerson,

You can do the same using following steps:

1) \Create a varible on universe

count = count(distinct month)

2) On ay webi report,create a separate query and drag newly created object count;

Now you this perticular object where ever you require.

Thanks,

Swapnil