Calculate distinct values ignoring one dimension

[#1]

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!

[swuehl]

Remove the dimension?

[CELAMBARASAN]

Hi,

     Try with dollar expansion like below

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

Celambarasan

[#4]

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

[jagan]

Hi,

Try this expression

=Count( distinct TOTAL VALUE)

Regards,

Jagan.

[#6]

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

[swuehl · Accepted solution]

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.

[jagan]

Hi,

Can you attach sample file.

Regards,

Jagan.

[#9]

swuehl: thanks! that does the work perfectely!!