Count Problem

ck14nobel — Thu, 08 Jun 2017 13:34:54 GMT

Hi,

this is my first post after learning a lot from this forum. Thanks a lot.

I hope you can help me with this problem, because I didn't find a solution yet.

I have a table of order positions with OrderIDs and CustomerIDs. Each Order can have multiple rows (order positions) in the table.

In a pivot table I have to answer the following questions:

- How many orders do I have? --> Count (DISTINCT OrderID)

- How many customers do I have? --> Count (DISTINCT CustomerID)

- How many customers ordered in exactly one order???

- How many customers ordered in multiple orders???

This is a short extract of my table:

As you can see customer 1010522 has two orders (1770372 and 1772382), customer 1012682 has three orders.

So I have two customers with multiple orders and three customers with exactly one order. That's what I need expressions for.

A lot of thanks in advance.

Re: Count Problem

sunny_talwar — Thu, 08 Jun 2017 15:15:43 GMT

May be this:

- How many customers ordered in exactly one order??? --> Count (DISTINCT {<CustomerID = {"=Count (DISTINCT OrderID) = 1"}>} CustomerID)

- How many customers ordered in multiple orders??? --> Count (DISTINCT {<CustomerID = {"=Count (DISTINCT OrderID) > 1"}>} CustomerID)

ck14nobel — Fri, 09 Jun 2017 07:53:18 GMT

Many thanks Sunny, that works for me.