8 Replies Latest reply: Apr 13, 2012 10:08 AM by Mathias Vanden Auweele

# Calculate distinct values ignoring one dimension

Dear all,

I want to make a chart with the count of the distinct values in my dataset over a certain time period. So I have an expression count( distinct VALUE) and a dimension MONTH.

However, when I define such a chart I get the count of distinct VALUE per MONTH and not over the total period. I only want to see 1 entry of VALUE in my entire chart. How can I realise such a thing?

Thanks!

• ###### Calculate distinct values ignoring one dimension

Remove the dimension?

• ###### Re: Calculate distinct values ignoring one dimension

Hi,

Try with dollar expansion like below

use your expression as \$(=count( distinct VALUE))

Celambarasan

• ###### Calculate distinct values ignoring one dimension

Thanks for your fast replies. However:

@swuehl: then my chart wouldn't have a dimension...

@Celambarasan Adhimulam: that ignores my dimension completely. I still want the count to be calculated for every MONTH but it shouldn't include the VALUES already counted in the previous MONTHs

• ###### Calculate distinct values ignoring one dimension

Hi,

Try this expression

=Count( distinct TOTAL VALUE)

Regards,

Jagan.

• ###### Calculate distinct values ignoring one dimension

That gives me the same result as using the dollar sign expansion

• ###### Re: Calculate distinct values ignoring one dimension

You could use something like

=count (distinct aggr(ID, ID))

as expression (see attached sample). aggr() function will list your IDs in load order and determine based on this if a values is already read in. So to make it work, your load order of IDs should be also in chronological order.

Hope this helps,

Stefan

edit:

even a

count( aggr(ID,ID))

should / could be enough, the distinct we need is implicitely part of the aggr() function.

• ###### Re: Calculate distinct values ignoring one dimension

swuehl: thanks! that does the work perfectely!!

• ###### Calculate distinct values ignoring one dimension

Hi,

Can you attach sample file.

Regards,

Jagan.