7 Replies Latest reply on Oct 11, 2016 2:41 PM by David Moore

# Unique occurrance within results

Good day team,

I have a list of accounts that might have a  combination of products, I am looking for a way to find the accounts that have only one type of product. For example:

Customer
Qty of Product A
Qty of Product B
Qty of Product C
Customer #111
Customer #221
Customer #32

In this example, I'm looking for the total quantity of Customers that have ONLY product A, which would be one. Both Customer #1 and Customer #3 have product A, but Customer #1 also has Product C as well. My need is to isolate customers with only Product A.

Any suggestions?

Many thanks!

Dave M.

• ###### 1. Re: Unique occurrance within results

In this case, The simplest way is using LOD as following.

[Flag only a]

if{fixed [Customer]:sum([Value])}={fixed [Customer]:sum (if [Product]="a" then [Value] end)} then

"only a" else "multiple" end

Thanks,

Shin

1 of 1 people found this helpful
• ###### 2. Re: Unique occurrance within results

Hey David,

In addition to Shin's solution, this LOD will also work:

if {fixed [Customer Name]: countd([Category])} = 1 then 1

else 0 END

You can then click Analysis > Totals > Show Column Grant Totals.

Hope this helps!

-Lisa

1 of 1 people found this helpful
• ###### 3. Re: Unique occurrance within results

Shinichiro,

I really like your thought process here, and I think your solution will likely be a great one for David!

But I do want to caution all of us not to jump to the conclusion that LOD calcs are always required or will always work as intended.  In fact, there are several assumptions that if not true, would render the proposed solution wrong or overly complex:

1. If the shape of the data is as shown in the question (e.g. one record per customer with a Quantity column for each product), then only a simple row-level calculation is needed.  This is probably not the case, but definitely worth checking.
2. If the data is not at the Customer level but the final view will be at the Customer level of detail, then a simple aggregate calculation will work.
3. If David intends to filter the data in any way (e.g. Check to see if a customer has only Product A for a given time period), then he needs to be cautioned that he will have to used context filters with FIXED level of detail calculations.  Otherwise, those calculations will not be affected by the filter.  And this complexity can be avoided in either case #1 or #2.
4. If Quantity can ever be negative (not likely with a quantity field, but imagine if returns are counted as negative values), then your calculation can yield false positives (e.g. a customer has 2 of A, 1 of B and -1 of C)  The overall sum is 2 and the sum for A is 2 making it appear that the customer only has A.  Again, not likely, but an assumption that should be ruled out.  This assumption would need to be checked for any row-level or aggregate calculation solutions too.

At any rate, I have the feeling your calculation will work really well and will satisfy David's need.  I just know that I have a tendency to jump to an LOD as the solution for everything (just like I used to jump to table calc solutions) and often the complexity can be avoided.

Hope this helps!

Joshua

2 of 2 people found this helpful
• ###### 4. Re: Unique occurrance within results

Joshua,

I agree. Thank you for the suggestions and consideration !!

I appreciate these kind of feedbacks.

Thanks,

Shin

1 of 1 people found this helpful
• ###### 5. Re: Unique occurrance within results

Shin, thank you so much for your suggestion. It leads me into the wonderful world of LOD! I appreciate your help.

Regards,

Dave M.

• ###### 6. Re: Unique occurrance within results

Hi Lisa,

Thank you so much for your suggestion and help! Greatly appreciated!

Regards,

Dave M.

• ###### 7. Re: Unique occurrance within results

Hi Joshua,

Thank you for lending your expertise, so many things I would never have thought about! Your help is much appreciated!

Regards,

Dave M.