Count of IDs with criteria

[#1]

I have a table wich includes the following columns as well as others

ID | Agentkey | trans_num |

There are many cases where for one ID there may be multiple rows

ID | Agentkey | trans_num |

12| 44455544 | 953213213 |

12| 44455521 | 953213213 |

12| 44455522 | 953213213 |

12| 44455540 | 953213213 |

13| 44455544 | 953213213 |

13| 44455503 | 953213213 |

13| 44455512 | 953213213 |

15| 44455544 | 953213213 |

I want to design a filter based on the count of agent keys per ID in this case the available filters would be 4,3 and 1.

4 agent keys associated with ID 12,

3 with ID 13 and

1 with ID 15

What would be the best way to accomplish this?

I thought Count(Total<ID> Agentkey) would work but it doesn't

Any help would be appreciated.

Rob

[sivarajs]

Check the attachment

[#3]

Sivaraj, I am currently running personal edition we are working on a server and full setup I cannot view a file created by someone else.

[#4]

Hi,

Take Id as dimension  and expression as count (distinct AgentKey)

[#5]

Problem here is there are over 6 million IDs. There should be a limited count of distinct agentkeys per ID between 0 and 9

[sivarajs]

Just take a listbox--> expression --> aggr(count(Agentkey),ID)

[#7]

Hi,

You can do this:

Go to the ID list box--Properties-Expression tab and add Count(AgentKey) as expression.

This will give you an ID filter with count of each Agent Key.

Regards,

-Khaled.

[#8]

Hi Robflint,

Sorry I am not getting your requirement ,as per your question above solution works . Can you explain in detailed way

[#9]

I think I need to state the final result requirement a little clearer. I have over 6 million IDs I only want to see the possible counts of agentkey

and to be able to filter all records that match those counts so if there are 4 million IDs with a count of 3 agentkeys I can click 3 and it will limit my data set to only those records. I don;t need to scroll through 6000000 rows highlighting all the counts of agent key that I want to see.

[CELAMBARASAN]

Hi,

In load script try with this

Load

ID,

Count(Agentkey) AS AgentKeyFilter

Resident Table1

Group by ID;//Table1 is the table with ID,Agentkey,trans_num fields

Add AgentKeyFilter list box in your sheet.