Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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.
Remove the dimension?
Hi,
Try with dollar expansion like below
use your expression as $(=count( distinct VALUE))
Celambarasan
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
Hi,
Try this expression
=Count( distinct TOTAL VALUE)
Regards,
Jagan.
That gives me the same result as using the dollar sign expansion
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.
Hi,
Can you attach sample file.
Regards,
Jagan.
swuehl: thanks! that does the work perfectely!!