Count issues

[#1]

Hi folks, I need again help for this forum. Now I'm facing issues with a count sentence.

I'm trying to count using some filters but the result is not correct.

Having the next sentence:

=Num(Count(Distinct {$<[Fiscal Year]= , [Fiscal Period]= ,Category=, Vendor={'AAA'}>} [MSR Customer Name]&TotalRoom), '#,##0')

The idea is to get how many different MSRCustomerName&TotalRoom do we have for vendor AAA. The reason why I'm concatenating CustomerName and TotalRoom is 'cos we do have duplicated customername, so adding the numbers of room I can see individuals.

Anyway, the previous sentece is returning 4 as a result, meanwhile the next one:

=Num(Count(Distinct {$<[Fiscal Year]= , [Fiscal Period]= ,Category= ,Vendor={'Other'}>} [MSR Customer Name]&TotalRoom

), '#,##0')

return 12. So far I now that the result is not right but the extrange behavior is when I try the next sentence:

=Num(Count(Distinct {$<[Fiscal Year]= , [Fiscal Period]= ,Category= ,Vendor=>} [MSR Customer Name]&TotalRoom), '#,##0')

Which is for getting the whole quantity of customers. This one is returning 14.

So, any idea or suggestion to fix this issue would be very appreciated.

Thanks in advance!

PS: BTW, I only have 2 vendors in my DB. AAA & Other

[Anonymous]

There is nothing strange about it.  If AAA returns 4, and Other returns 12, the whole count can be anywhere between 12 (if all 4 AAA exist in Other as well)  and 16 (if AAA and other have no overlaps).

Regards,

Michael

[#3]

Thanks Michael,

You right! I didn’t think in that way. Do you know then how can I get the sum of both? It could be literal Count1 + Count2?

Diego Esteban Adum

APLA BI Manager

[Anonymous · Accepted solution]

Diego,

It can be count1 + count2, or sum(aggr(count(...),Vendor))