topic Limiting Count. in QlikView

Limiting Count.

Mon, 24 Oct 2011 08:13:55 GMT

I have the following fields,Participant ID, Country and Month. I want to find total number of participants per country.

Using the following Definition ( Count (DISTINCT PARTICIPANT_ID) ) I could able to extract relevant information. My problem is in certain countries we have only 1 or 2 particpants. In certain countries we have more than 1000 participants. I want to extract only > 25 participants in chart and anything <25 should be summarised under Others.

How do I implement this? (Attached file contains teh data, Country, Participants) It is difficult to see as we have more countries.

Thanks

Re: Limiting Count.

Miguel_Angel_Baeyens — Mon, 24 Oct 2011 08:18:59 GMT

Hi,

I'd try the following as an expression

Count({< PARTICIPANT_ID = {"=Count(DISTINCT PARTICIPANT_ID) > 25"} >} DISTINCT PARTICIPANT_ID)

And even better creating this accumulation in the script using Peek() and Previous() as a flag field, (with values 1 if greater than 25 or 0 if less), so although the load time would be a bit slower, the chart rendering will do faster. Check this thread among several others on how to create accumulations in the script using those functions.

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

Limiting Count.

its_anandrjs — Mon, 24 Oct 2011 08:21:44 GMT

Hi,

I suggest you have to use the condition in expression like

Count(If( Count (Distinct PARTICIPANT_ID) ) > 25, Count (Distinct PARTICIPANT_ID) ) ) )

this will show only > 25 participants like ways

Count(If( Count (Distinct PARTICIPANT_ID) ) < 25, Count (Distinct PARTICIPANT_ID) ) ) )

this will show only < 25 participants like ways

Rgds

Anand

Re: Limiting Count.

Miguel_Angel_Baeyens — Mon, 24 Oct 2011 08:30:22 GMT

Hi there,

Going even further, and although I don't recommend you to do this way because of the presumable poor performance of calculated dimensions, you could leave the expression as it is, but use the following as dimension in the chart

If(Aggr(Count(DISTINCT PARTICIPANT_ID), PARTICIPANT_ID) > 25, PARTICIPANT_ID, 'Others')

Check syntax, I may have missed some parentheses.

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

Re: Limiting Count.

its_anandrjs — Mon, 24 Oct 2011 08:33:34 GMT

Hi,

You can use it another ways also by aggregating the data like

Count(If( Count (Distinct PARTICIPANT_ID) ) > 25, Aggr( Count (Distinct PARTICIPANT_ID),PARTICIPANT_ID) ) ) )

this will show only > 25 participants like ways

Count(If( Count (Distinct PARTICIPANT_ID) ) < 25, Aggr( Count (Distinct PARTICIPANT_ID),PARTICIPANT_ID) ) ) )

this will show only < 25 participants like ways

Rgds

Anand

Limiting Count.

Tue, 25 Oct 2011 02:37:22 GMT

Thanks for the help

If I change the Definition

From: Count (DISTINCT PARTICIPANT_ID)

To: If(Count(Distinct PARTICIPANT_ID) >=25, Count ( DISTINCT PARTICIPANT_ID))

I am getting the Graph but it is not summerised under Others.

Please refer to the below Image fb1.JPG

If change this to

If(Count(Distinct PARTICIPANT_ID) >=25, Aggr( Count ( DISTINCT PARTICIPANT_ID),PARTICIPANT_ID) )

Aggr(Count(DISTINCT PARTICIPANT_ID), PARTICIPANT_ID)

I am getting Graph similar to below. except in Aggr(Count(DISTINCT PARTICIPANT_ID), PARTICIPANT_ID) I am getting all countries.

If I use

Count({< PARTICIPANT_ID = {"=Count(DISTINCT PARTICIPANT_ID) > 25"} >} DISTINCT PARTICIPANT_ID)

I am getting answer no data to display.

if I use

If(Count(Distinct PARTICIPANT_ID) >=25, Count ( DISTINCT PARTICIPANT_ID), 'Others')

I think we should modify the folloing

If(Count(Distinct PARTICIPANT_ID) >=25, Count ( DISTINCT PARTICIPANT_ID))

to add additional Row 'Others' and it should be incremented in condition is met. How should I implement?

Thanks