Distinct Count in chart, wanting Distinct count by dimension

Thu, 03 May 2012 18:01:33 GMT

Say I have a chart as follows

Apr Mar

1 1

1 2

1 3

2 4

3 5

Distinct Count 3 5 Total 5

How can I get a distinct count by Month as the Total? I want the result for the Total to be 8, 3 from Apr and 5 in Mar.

I am using this expression

Count(Distinct Numbers)

What I am trying to do is get an average but I have not been able to get the avg and aggr function to work so I am trying to create the averages myself.

I also tried using set analysis to get all of the months Distinct counts and then divide by the total Month/Year combinations as follows.

=((count({$<[Backlog Month] = {"Jan"}>}Distinct [Claim Number]) +

(count({$<[Backlog Month] = {"Feb"}>}Distinct [Claim Number]) +

(count({$<[Backlog Month] = {"Mar"}>}Distinct [Claim Number]) +

(count({$<[Backlog Month] = {"Apr"}>}Distinct [Claim Number]) +

(count({$<[Backlog Month] = {"May"}>}Distinct [Claim Number]) +

(count({$<[Backlog Month] = {"Jun"}>}Distinct [Claim Number]) +

(count({$<[Backlog Month] = {"Jul"}>}Distinct [Claim Number]) +

(count({$<[Backlog Month] = {"Aug"}>}Distinct [Claim Number]) +

(count({$<[Backlog Month] = {"Sep"}>}Distinct [Claim Number]) +

(count({$<[Backlog Month] = {"Oct"}>}Distinct [Claim Number]) +

(count({$<[Backlog Month] = {"Nov"}>}Distinct [Claim Number]) +

(count({$<[Backlog Month] = {"Dec"}>}Distinct [Claim Number])) /

TextCount(Distinct [Backlog Month/Year Combo])

The reason I cant get this to work is that if you select the Backlog Month as Apr, it does not react because the SET is for all months.

So, I would like the second scenario to work properly when selecting [Backlog Month] or I will need a solution for the first scenario to do a distinct Count by Backlog Month.

Does anyone have any ideas. Thanks in advance......Troy

Distinct Count in chart, wanting Distinct count by dimension

Thu, 03 May 2012 18:07:07 GMT

Try

Sum(aggr(count(distinct [Claim Number]),Month) That should fix the total.

If you want a rolling average you could try

rangeavg(above(count(distinct [Claim Number]),0,rowno(total))

Distinct Count in chart, wanting Distinct count by dimension

Thu, 03 May 2012 20:07:58 GMT

Thanks, that is it. I got the Aggr function working.

topic Distinct Count in chart, wanting Distinct count by dimension in QlikView

Distinct Count in chart, wanting Distinct count by dimension

Distinct Count in chart, wanting Distinct count by dimension

Distinct Count in chart, wanting Distinct count by dimension