2 Replies Latest reply on Aug 30, 2017 4:35 AM by Leanne Vermeulen

# Highlight Individual Attributes In A Field If They Occur More Than X Times

Hi, a client wants to know if customers purchased any particular product more than x-amount of times. The calculation they want would then show that for every instance (per customer) where a product was purchased more than x-times, this would count as 1. If it happens for that same customer that another product is purchased more than x-times, the number would be 2, and so on. So, the data is laid out that you have the Product field and then obviously the individual product names within that field. Is there a way to highlight the number of times each product was purchased per customer and assign each instance a value of "1".

I'm thinking I can fix "Product" to "Number of Records" and create a formula that says if SUM > x THEN 1, etc. and then fix that to "Customers". But I'm not sure that will work per each instance of a product?

Results also needs to be filterable by year selected. Any ideas?

• ###### 1. Re: Highlight Individual Attributes In A Field If They Occur More Than X Times

hi Leanne,

So I think I understand what you are after...and I think your general approach will work.

What I've done here is create the following LoD

[Product Quantity per Product/Customer]

{FIXED [Customer Name], [Product Name],[Order Date Year]: SUM([Quantity])}

I then created a parameter so you can change the "how many of each product were bought by each customer/year"

and then created this calculation to count the customers...

[Count of Customers with N Purchases of a Product Per Year]

COUNTD

(

IIF([Product Quantity per Product/Customer]>=[Number of Purchases], [Customer Name],NULL)

)

If we look at one example, with parameter set to 5 (units)

We can see

There were 2 customer who bought 5 or more

Hope that's what you were after, or can adapt this logic to your exact situation

1 of 1 people found this helpful
• ###### 2. Re: Highlight Individual Attributes In A Field If They Occur More Than X Times

That is PERFECT, thank you so much Simon!