Question Re: Counting distinct. in Technology Q&A

Counting distinct.

former_member214071 — Fri, 10 Apr 2015 20:43:37 GMT

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.

Re: Counting distinct.

Former Member — Sun, 12 Apr 2015 15:19:44 GMT

To count distinct values you can write down formula

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

Thanks,

Swapnil

Re: Counting distinct.

former_member214071 — Mon, 13 Apr 2015 14:15:21 GMT

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.

Re: Counting distinct.

Former Member — Mon, 13 Apr 2015 14:28:38 GMT

Can you please let me know your exact requirement?

Why you need this on universe?

Thanks,

Swapnil

Re: Counting distinct.

former_member214071 — Mon, 13 Apr 2015 14:35:04 GMT

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

Re: Counting distinct.

Former Member — Tue, 14 Apr 2015 12:34:20 GMT

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