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

    Unique occurrance within results

    David Moore

      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:


      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
          Shinichiro Murakami

          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





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

            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

            unique values.PNG

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


            Hope this helps!


            CoEnterprise | Blog

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



              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!


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



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

                I appreciate these kind of feedbacks. 




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

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


                  Dave M.

                  • 6. Re: Unique occurrance within results
                    David Moore

                    Hi Lisa,

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


                    Dave M.

                    • 7. Re: Unique occurrance within results
                      David Moore

                      Hi Joshua,


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


                      Dave M.