topic Re: Total distinct count in QlikView

Total distinct count

curiousfellow — Thu, 02 Jul 2015 09:52:57 GMT

I want to use the sum of a distinct count based on two values.

For example my values are

recno	dept	val
1	dept1	val1
2	dept1	val2
3	dept1	val1
4	dept2	val1
5	dept1	val1

So the distinct count for dept1 should be 2

When I use the expression Sum(Aggr(Count(Distinct dept),val)) I get the correct value when I only use [dept] as dimension in a pivot table.

However : I want to use [val] as a dimension too.

How do I have to change my expression ?

Re: Total distinct count

alexandros17 — Thu, 02 Jul 2015 09:56:14 GMT

Count(distinct(dept & val))

Re: Total distinct count

MK_QSL — Thu, 02 Jul 2015 10:08:07 GMT

COUNT(DISTINCT TOTAL <dept> val)

COUNT(DISTINCT TOTAL <val> val)

Re: Total distinct count

curiousfellow — Fri, 03 Jul 2015 09:39:24 GMT

This works fine when no selection is made. Probably I misdefined my question.

This is what i need :

I have to count every record per year,per dept.

The result should not change when a selection is made. In a chart [val] has to be multiplied with the result of the count.

Because I am still struggling with the "set analysis" options, I don't know how to solve this

Re: Total distinct count

hic — Fri, 03 Jul 2015 09:46:33 GMT

You should probably not use Aggr() but instead use set analysis.

Count ( {1} distinct Val )

See The Aggregation Scope

HIC

Re: Total distinct count

berkarmagan — Sat, 01 Oct 2016 14:58:24 GMT

Hi, how can i use sth like that; count({1}distinct val), it doesnt work like this. What i need is on my post which is Pivot Table total func problem‌. Thanks for now.