topic Re: Accumulated Distinct Count in QlikView

Accumulated Distinct Count

Anonymous — Thu, 09 Jun 2016 22:46:41 GMT

Hi all,

Any idea how to get accumulated distinct count ?

Column Count is the result that I need to receive in QlikView.

Distinct Count for period 1 = 5,

Distinct Count for period 1+2 =7

Distinct Count for period 1+2+3 = 8

Thanks for your help.

Re: Accumulated Distinct Count

swuehl — Thu, 09 Jun 2016 23:09:43 GMT

For example like this:

INPUT:

LOAD * INLINE [

Period, Name

1,A

1,B

1,C

1,D

1,E

2,F

2,G

2,A

2,B

2,C

2,D

3,H

3,A

3,B

3,C

];

LOAD DISTINCT Period as PeriodAsOF, iterno() as Period

RESIDENT INPUT

WHILE IterNo() <= Period;

PeriodAsOF	COUNT(DISTINCT Name)
	8
1	5
2	7
3	8

The As-Of Table

Re: Accumulated Distinct Count

Anonymous — Thu, 09 Jun 2016 23:22:26 GMT

Is it possible to get that result without using script? Using Straight Table for example with set analysis?

Re: Accumulated Distinct Count

swuehl — Fri, 10 Jun 2016 00:14:28 GMT

The script is only preparing the AsOf Table for the Periods (Don't get mislead by the DISTINCT keyword, there are also other approaches to create the same table as shown in the blog post).

I believe using the AsOf table would be the easiest approach (and the table can also be used for other accumulations, see the examples in the blog post).

Why do you want a front end only solution?

Re: Accumulated Distinct Count

swuehl — Fri, 10 Jun 2016 00:23:42 GMT

I don't think a set analysis solution will really work here, since set analysis is only evaluated once per chart.

You can create an expression with a set expression for every period (like emulating the as of table in the front end), but I wouldn't do this.

Depending on your data model, you might be able to make use of the DISTINCTness of the aggr() function, so this might work without a change to your original data model:

Period	Count(Aggr(Name,Name))
	8
1	5
2	7
3	8

[full accumulation enabled in the expression settings.]

See attached a sample for both options.

Re: Accumulated Distinct Count

Anonymous — Fri, 10 Jun 2016 11:17:28 GMT

‌thanks swuehl. It works. Is there any way not to use 'Full accumulation' and get that result? Cause when I want to sort by Period descending results are no longer correct.

Re: Accumulated Distinct Count

swuehl — Fri, 10 Jun 2016 11:26:17 GMT

The AsOf table approach will handle these kind of issues correctly.

Re: Accumulated Distinct Count

mgranillo — Wed, 15 Jun 2016 16:14:05 GMT

Thomas,

Try rangesum(above(sum( aggr( rangesum( count(distinct "Name"))),"Name")),0,RowNo()))

referenced from: Count Distinct Accumulation in Qlik Sense

You will need to sort your table descending in the load script though and sort your chart descending to get the right numbers.

Re: Accumulated Distinct Count

Tue, 21 Mar 2017 17:54:48 GMT

Hi Stefan,

I used your useful tip for this issue. I applied it in the following line chart expression:

aggr(rangesum(above(num(count({<Month={'<=$(=max(Month))'}>}aggr(distinct Name,Name),'#.##0'),0,RowNo()),Month))

I can not realise why this expression works well on my local pc, while it changes dramatically when published on the access point.

Did you notice similar issues?

Thanks

Re: Accumulated Distinct Count

swuehl — Tue, 21 Mar 2017 19:17:00 GMT

The aggr() function sorts its dimensional values by load order by default.

You need to ensure that Month is in correct load order (i.e. it must be chronological load order) or you need to use

The sortable Aggr function is finally here!

Re: Accumulated Distinct Count

Wed, 22 Mar 2017 14:09:13 GMT

Hi Stefan,

my expression: does not entail Month into the aggr().

=if(Count([Name])<>0,rangesum(above(sum({<Month={'<=$(=max(Month))'}>}aggr(distinct count({<Month={'<=$(=max(Month))'}>} distinct [Name]),[Name])),0,RowNo())))

How would you write it to make it work correctly?

Thanks,