Help please - Difficulties to create a Table with the Right Info

[Anonymous]

Hello again,

  I have this data:

CompanyId	GroupId	OrderId
C01	G01	1
C01	G02	2
C01	G05	3
C02	G02	1
C02	G04	2
C03	G01	1
C03	G02	2
C03	G05	3
C03	G06	4
C03	G07	5

And I need to show a table that will show the number of distinct companies grouped by GroupId. The important thing is that a company has to be counted only once in the available group with the maximum OrderId.

So here you are some examples:

1) With no filters

GroupId	NumDistinctCompanies
G01	0
G02	0
G03	0
G04	1
G05	1
G06	0
G07	1
TOTAL	3

2) Filtering groups G01, G02 and G06.

GroupId	NumDistinctCompanies
G01	0
G02	2
G06	1
TOTAL	3

Do not hesiate to ask if you have any doubt and many many thanks in advance.

Javier

[chiru_thota]

Please have look at attached QVW . But I am not able to understand your question (2)

[swuehl]

Maybe using a dimension GroupId and an expression

=sum(aggr(if(OrderId= max(total<CompanyId> OrderId),1),CompanyId, GroupId))