Count total distinct values over a combination of dimensions

[rzaruvne]

The expression below counts the distinct values of dim guiaSolicitacaoInternacao, using TOTAL to disregard the chart dimensions.

Count(
TOTAL DISTINCT {
<
origemEventoAtencao = {'1','2','3'},
tipoInternacao = {'1','2','3','4','5'},
regimeInternacao = {'1','2'},
guiaSolicitacaoInternacao = {"*"},
dataRealizacao = {">=$(=Date(AddMonths(Max(dataCalendario), -12)))<=$(=Date(Max(dataCalendario)))"},
tipoRegistro = {'1','2'},
IsUltimaVersao = {'1'},
[Ultima incorporada] = {'1'}
>
} guiaSolicitacaoInternacao
)

I would like to take into account the combination of dimensions CNPJ, CNES, IBGE, that is, for each combination, count each guiaSolicitacaoInternacao once. There may be a case where there are two rows in my data with the same value for guiaSolicitacaoInternacao, but a different combination of CNPJ, CNES, IBGE, so the count would be 2 instead of 1

[rubenmarin · Accepted solution]

Hi, if the dimensions are on the chart you can add them to TOTAL between < and >:

Count(
TOTAL <CNPJ, CNES, IBGE> DISTINCT {
<
origemEventoAtencao = {'1','2','3'},...

If not, or as an alternative, you can do an aggr to calculate each combination and sum all the combinations:

Sum(Aggr(Count(
TOTAL DISTINCT {
<
origemEventoAtencao = {'1','2','3'}...
} guiaSolicitacaoInternacao
),CNPJ, CNES, IBGE))

[rzaruvne]

Hi, thanks for your reply. It did work, but i had to remove the TOTAL from the expression.

[rubenmarin]

True, I missed that part.