topic Count total distincts from two tables in App Development

Count total distincts from two tables

wanyunyang — Fri, 02 Apr 2021 08:17:11 GMT

I have one order table with OrderNo, one invoice table with InvoiceNo and OriginalOrderNo.

I would like to count the total distinct order numbers from both tables. I'm using:

Count(distinct OrderNo) + Count(distinct OriginalOrderNo)

But the problem is, there would be partial invoices. For example, OrderNo1 has been partially invoiced, it will still be in order table with OrderNo1, as will as be in invoice table as InvoiceNo1 and OriginalOrderNo1. So it should be counted once. However, in my function the result is 2.

How should I update my function? Any advice helps! Thanks in advance!

Re: Count total distincts from two tables

jwjackso — Fri, 31 Jan 2020 22:44:33 GMT

Try the P Function

Count(distinct OrderNo) + Count({<OriginalOrderNo-=P(OrderNo)>}distinct OriginalOrderNo)

Re: Count total distincts from two tables

wanyunyang — Fri, 31 Jan 2020 22:47:40 GMT

Hi,

Thank you for your reply! I tried this function but didn't work. I think it's because I have set analysis like:

Count({<OrderDate={'01/01/2020'}>}distinct OrderNo) +

Count({<OriginalOrderNo-=P(OrderNo),OrderDate={'01/01/2020'}>}distinct OriginalOrderNo)