topic Calculate distinct values ignoring one dimension in QlikView

Calculate distinct values ignoring one dimension

Thu, 12 Apr 2012 10:31:52 GMT

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

swuehl — Thu, 12 Apr 2012 10:36:18 GMT

Remove the dimension?

Re: Calculate distinct values ignoring one dimension

CELAMBARASAN — Thu, 12 Apr 2012 10:41:15 GMT

Hi,

Try with dollar expansion like below

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

Celambarasan

Calculate distinct values ignoring one dimension

Thu, 12 Apr 2012 10:51:18 GMT

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

jagan — Thu, 12 Apr 2012 10:59:47 GMT

Hi,

Try this expression

=Count( distinct TOTAL VALUE)

Regards,

Jagan.

Calculate distinct values ignoring one dimension

Thu, 12 Apr 2012 11:25:29 GMT

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

Re: Calculate distinct values ignoring one dimension

swuehl — Thu, 12 Apr 2012 11:30:04 GMT

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.

Calculate distinct values ignoring one dimension

jagan — Thu, 12 Apr 2012 11:31:37 GMT

Hi,

Can you attach sample file.

Regards,

Jagan.

Re: Calculate distinct values ignoring one dimension

Fri, 13 Apr 2012 14:08:17 GMT

swuehl: thanks! that does the work perfectely!!