Count results of Aggr() function

[#1]

Hi,

I am quite new to QlikView, could anyone help on task below.

I've got query which returns right figures:

=Aggr(Count(DISTINCT [Framework Description]),[Customer No_])

This returns member name and the number that on frameworks dealing.

Could anyone tell me how to count in query how many members dealing only in 1 framework, then in 2 and etc? And last one should be 10 or more? Also null value should be excluded here.

Example of data:

Based on example data above Members dealing in 9 Frameworks should be 11 and on 10 or more Frameworks should be 13. Counts for each framework will be displayed in each separate label.

Thanks in Advance.

[MK_QSL]

Provide some sample data...

You need to use Aggr, Count in Calculated Dimension...

Create a Straight Table

Calculated Dimension

=Aggr(COUNT(DISTINCT [Framework Description]),[Customer No])

Expression

Count(DISTINCT [Customer No])

[#3]

This is would be an example of simplified data:

SampleData:

Load * inline
[
Member_Code, Framework
A1,FW1
A1,FW2
A1,FW3

A2,FW2
A2,FW3
A2,FW4
A2,FW5

A3,FW3
A3,FW3

A4,FW4

A5,FW5

A6,FW5

A7,FW1
A7,FW5
];

From this example should return results:

Members dealing on 1 Framework: 3 (Members A4+A5+A6)

Members dealing on 2 Framework: 2 (Members A3+A7)

Members dealing on 3 Framework: 1 (Member A1)

Members dealing on 4 Framework: 1 (Member A2)

I need a query for each line only.

Does it help?

[MK_QSL]

Calculated Dimension

=Aggr(COUNT(Framework),Member_Code)

Expression

COUNT(DISTINCT Member_Code)

[#5]

You've provided data for table. I need to get just one figure in the label which should look like this for members in a single framework

=Count(
{$< Aggr(Count(DISTINCT [Framework Description]),[Customer No_]) = {"1"}  >}
Aggr(Count(DISTINCT [Framework Description]),[Customer No_])
)

For the count of members in 2 frameworks

=Count(
{$< Aggr(Count(DISTINCT [Framework Description]),[Customer No_]) = {"2"}  >}
Aggr(Count(DISTINCT [Framework Description]),[Customer No_])
)

Unfortunately this syntax is not acceptable in Qlik.

[MK_QSL]

=COUNT(Aggr(IF(COUNT(Framework)=1,Member_Code),Member_Code))

=COUNT(Aggr(IF(COUNT(Framework)=2,Member_Code),Member_Code))

=COUNT(Aggr(IF(COUNT(Framework)=3,Member_Code),Member_Code))

=COUNT(Aggr(IF(COUNT(Framework)=4,Member_Code),Member_Code))

[#7 · Accepted solution]

Close but it does not return correct figures.

After playing with your suggestion got the right answer:

=Sum(IF(Aggr(Count(DISTINCT [Framework Description]),[Customer No_])>=10, 1))

[MK_QSL]

I haven't used Distinct, because if I use Distinct, would not able to match your required result as per the data you have provided.