11 Replies Latest reply: May 23, 2017 9:27 AM by Luis Carmona Martínez RSS

    Customers who bought 2 Products and nothing else in same category

    Luis Carmona Martínez

      Hi,

       

      I need to get the SUM os Sales from the Customers who bought 2 products and nothing else from the same category. In this case there are several products and they are organized in different categories.

       

      I am able to get the Sum of Sales from the customers who bought 2 products, but I am not able to exclude the ones that also bought other products from the same category

       

      This will the sum sales in

       

      sum( {$<CLIENT CODE = P({1<[PRODUCT CODE]={"901","518"}>} CLIENT CODE) }>} [SALES] )

       

      But, I need to exclude from the formula above the customers who bought different products  from 901 and 518 and pertain to Category1. The expected result: SUM of sales (Customers that only bought Product 901 or 518 in Category1 but do include the sales from this customers in other categories.

       

      Any help??

        • Re: Customers who bought 2 Products and nothing else in same category
          Felip Drechsler

          Hi Luis,

           

          See the attached file to see if it helps.

           

          Best regards,

           

          Felipe. !

          • Re: Customers who bought 2 Products and nothing else in same category
            Luis Carmona Martínez

            Hi Felip Drechsler,

             

            Thanks for your answer, but I am not sure that this  is what I was looking for, I may have explained myself wrong.

             

            In the formula you displayed on the Qlik App (though I am using Qlik Sense)

             

            if

            (count(ClientCode)=2,

            sum({< ClientCode=P({<ProductCode={'901'}>}ClientCode)*P({<ProductCode={'518'}>}ClientCode),ProductCode={'901','518'}>} Sales ))


            The first condition count(clientCode)=2  it´s not what I need, because one customer could have bought products from different categories, meaning that its own code could have appeared several times more than 2 in the dataset. Also one customer could have bought products  901 and 518 several times, and that will increase the Count of ClientCode also.



            I hope this Example helps to understand the problem:


            "Client Code"  "Category Product Code "   "Product CodeSales

            001                     2                                          901                  101

            001                     2                                          518                  51

            001                    2                                          600                   201

            001                     3                                           350                  301

            002                     2                                          901                  102

            002                     2                                          518                  52

            002                     2                                          901                  202

            002                     3                                           350                  302

            002                     4                                           313                  502



            In the example above the expected Result:

            Sales from the Client 001, are ALL excluded because this costumer has bought also other Product Code from Category 2, that are different from "Product Code" 901 and 518.


            ALL Sales from the Client 002, should ALL be INCLUDED, because this customer has not bought other product codes different from 901 and 518 from category 2. All sales from customer 002 are included, this also included sales from other categories so: 102 +52 +202 +302+502



                              



              • Re: Customers who bought 2 Products and nothing else in same category
                Luis Carmona Martínez

                Hi Vineeth,

                 

                Thanks for your answer, but with the formulas on that App, I am still not being able to produce any difference from what I have at the moment.

                 

                The formula more close to what I need to produce is close to what I have at the moment, but not exaclty

                 

                sum( {$<CLIENT CODE = P({1<[PRODUCT CODE]={"901","518"}>} CLIENT CODE) }>} [SALES] )


                I need to sum the above and EXCLUDE customers who bought  901 or 518  and also other product codes from the Category to witch Product Codes 901 and 518 belong to.


                I think this example is helpful, Thanks in advance !!


                 

                "Client Code"  "Category Product Code "   "Product CodeSales

                001                     2                                          901                  101

                001                     2                                          518                  51

                001                    2                                          600                   201

                001                     3                                           350                  301

                002                     2                                          901                  102

                002                     2                                          518                  52

                002                     2                                          901                  202

                002                     3                                           350                  302

                002                     4                                           313                  502



                In the example above the expected Result:

                Sales from the Client 001, are ALL excluded because this costumer has bought also other Product Code from Category 2, that are different from "Product Code" 901 and 518.


                ALL Sales from the Client 002, should ALL be INCLUDED, because this customer has not bought other product codes different from 901 and 518 from category 2. All sales from customer 002 are included, this also included sales from other categories so: 102 +52 +202 +302+502



                  • Re: Customers who bought 2 Products and nothing else in same category
                    Jonathan Dienst

                    Perhaps

                    Sum({$

                      <CLIENT CODE = P({1<[PRODUCT CODE] = {"901","518"}> - 1<[PRODUCT_CODE] -= {"901","518"}>} CLIENT CODE)>

                    } [SALES])

                      • Re: Customers who bought 2 Products and nothing else in same category
                        Luis Carmona Martínez

                        Thanks Jonathan,

                         

                        But still is not what I need. On the formula there should be something regarding the Product Category to which Product Code 901 and 518 belong, that on the example above the "Category Product Code" is "2".

                         

                        Let´s explain it again in other words. I have some clients that could be labeled as "Lovers of Product 901 and 518", if I stop selling products 901 and 518 and I lose this clientes I need to value how much I will lose as a whole - because they buy products from other categories. There could be customers who have bought product 901 or 518 from category 2, but they are not Lovers of Product 901 or 518 if they also have bought other products from category 2, those ones do not interest me on this issue.

                         

                        The formula should include someway to include the LOVERS of Products 901 and 518 and their whole sales from the data set, but not the ones that do also have bought Products 901 and 518  and others Products  from category 2.

                         

                        "Client Code"  "Category Product Code "   "Product CodeSales

                        001                     2                                          901                  101

                        001                     2                                          518                  51

                        001                    2                                          600                   201

                        001                     3                                           350                  301

                        002                     2                                          901                  102

                        002                     2                                          518                  52

                        002                     2                                          901                  202

                        002                     3                                           350                  302

                        002                     4                                           313                  502



                        In the example above the expected Result:

                        Sales from the Client 001, are ALL excluded because this costumer has bought also other Product Code (600)from Category 2, that are different from "Product Code" 901 and 518. The expected result in this case will be "0", this is not he LOVER of 518&901 Product Code.


                        ALL Sales from the Client 002, should ALL be INCLUDED, because this customer has not bought other product codes different from 901 and 518 from category 2. This client is the LOVER of Product 901 and 518. All sales from customer 002 are included, this also included sales from other categories so: 102 +52 +202 +302+502