topic Re: Count results of Aggr() function in QlikView

Count results of Aggr() function

Wed, 05 Nov 2014 15:42:54 GMT

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.

Re: Count results of Aggr() function

MK_QSL — Wed, 05 Nov 2014 15:44:30 GMT

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])

Re: Re: Count results of Aggr() function

Wed, 05 Nov 2014 15:58:02 GMT

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

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?

Re: Count results of Aggr() function

MK_QSL — Wed, 05 Nov 2014 16:03:28 GMT

Calculated Dimension

=Aggr(COUNT(Framework),Member_Code)

Expression

COUNT(DISTINCT Member_Code)

Re: Re: Count results of Aggr() function

Wed, 05 Nov 2014 16:09:20 GMT

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.

Re: Re: Count results of Aggr() function

MK_QSL — Wed, 05 Nov 2014 16:19:25 GMT

=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))

Re: Re: Count results of Aggr() function

Wed, 05 Nov 2014 17:00:04 GMT

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))

Re: Count results of Aggr() function

MK_QSL — Wed, 05 Nov 2014 17:56:36 GMT

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.