7 Replies Latest reply on May 19, 2016 7:32 AM by Adam Crahen

# Distinct Count of Customers where Sales were over \$xxx by Time Period (Aggregated)

Hi,

Below is a (Superstore) count of customers by quarter.  How can I get a similar chart with but only distinct count of customers who have sales over \$xxx per quarter?

Thanks.

• ###### 1. Re: Distinct Count of Customers where Sales were over \$xxx by Time Period (Aggregated)

Hi Nick-

Can do this with an LOD and a parameter so you can change the Sales amount to x.

Parameter

Create a calc for quarter - won't have to do this step anymore after 10.0 comes out.  Woot Woot.

Quarter

DATETRUNC('quarter',[Order Date])

Sales Met LOD

{fixed [Quarter], [Customer Name] : SUM([Sales])} > [Parameters].[Sales]

Put Sales Met LOD on filters and select True.

9.3 attached.

1 of 1 people found this helpful
• ###### 2. Re: Distinct Count of Customers where Sales were over \$xxx by Time Period (Aggregated)

hi,

Thank you for the response and workbook.  Follow up: what if I wanted several lines on one chart? ie. Total Customers, Total Customers with sales over \$2000, Total Customers with sales between \$1000 and \$2000, Total Customers with sales under \$1000?

Thanks.

• ###### 3. Re: Distinct Count of Customers where Sales were over \$xxx by Time Period (Aggregated)

Then Build a Calc like this:

Sales Group

IF {fixed [Quarter], [Customer Name] : SUM([Sales])} >= 2000 THEN "Sales Over \$2,000"

ELSEIF {fixed [Quarter], [Customer Name] : SUM([Sales])} <= 1000 THEN "Sales Less than \$1,000"

ELSE "Sales \$1,000 - \$2,000"

END

9.3 attached

1 of 1 people found this helpful
• ###### 4. Re: Distinct Count of Customers where Sales were over \$xxx by Time Period (Aggregated)

Thanks.  And then use this to add the total?

• ###### 5. Re: Distinct Count of Customers where Sales were over \$xxx by Time Period (Aggregated)

You could do a dual axis and remove the detail from the second marks card.  I dialed up size and made it gray.

Just be sure to sync the axis.  Then you can hide the 2nd one.

1 of 1 people found this helpful
• ###### 6. Re: Distinct Count of Customers where Sales were over \$xxx by Time Period (Aggregated)

Thank you very much!!!

What is fun too is that you can make your own brackets:

• ###### 7. Re: Distinct Count of Customers where Sales were over \$xxx by Time Period (Aggregated)

Sure.  Yes, two parameters and inject them in those calcs.