Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Please have look at attached QVW . But I am not able to understand your question (2)
Maybe using a dimension GroupId and an expression
=sum(aggr(if(OrderId= max(total<CompanyId> OrderId),1),CompanyId, GroupId))