12 Replies Latest reply: Mar 31, 2014 6:12 PM by Diego Adum RSS

    Opportunity view

      Hi, I'm working on a simple table on where I can show Customers sales and Qty of products sold. According to the detail below I have:

       

      CustomerProductSales
      Cust 1Prod 12
      Cust 1Prod 25
      Cust 1Prod 34
      Cust 1Prod 46
      Cust 2Prod 37
      Cust 2Prod 28
      Cust 2Prod 57
      Cust 3Prod 16
      Cust 3Prod 25
      Cust 3Prod 33
      Cust 3Prod 44
      Cust 3Prod 55
      Cust 3Prod 74
      Cust 3Prod 83
      Cust 3Prod 96
      Cust 3Prod 105
      Cust 4Prod 64

       

      NOTICE there are sales for the same product for different customers.

       

      My goal now is to add new columns on where I can show the remaining opportunity for those customers that are NOT buying the products that the KEY Customer is buying. In this case the KEY customer is the one that is purchasing more products (I don´t care about the sales amount, what really cares for this table is the Qty). So, the result should looks like the below table:

       

      CustomerSalesQty ProductsRemaining Opportunity
      Cust 34194
      Cust 117427
      Cust 222328
      Cust 44141

       

      Considering  Cust 1 (According to the detail information) we can see that is not buying 5 Products which the MIN SALES (which is the minimum amount of the invoice of all the customers) is equal to 27. I´m considering also the Prod 6 which is part of the market for these customers (although this product is not purchasing by the KEY customer)

       

      I think this is a very good exercise to get from QlikView Data Analysis beyond the regular usage.

       

      Hope somebody can help me to pointing me in the right direction.

       

      Thanks in advance!

        • Re: Opportunity view
          Manish Kachhia

          Can you tell us how you got 27 as Remaining Opportunity for Cust 1?

          Don't understand your explanation properly....

            • Re: Opportunity view

              Sure!!

               

              Cust 1 is purchasing Prod 1, 2, 3 and 4. So according to what is purchasing Cust 3 (which is the KEY customer) Cust 1 is missing to purchase Prod 5, 7, 8, 9 and 10 but also Prod 6 from Cust 4 (which is considered part of the group of customer in this exercise). So, considering the MIN Invoice value for the Products sold for the customers we got:

               

              Prod 5     $5

              Prod 6     $4

              Prod 7     $4

              Prod 8     $3

              Prod 9     $6

              Prod 10    $5

              TOTAL    $27

               

              Thanks for following this topic!

            • Re: Opportunity view
              Manish Kachhia

              Please check enclosed file...

               

              In case, you have personal edition... use below script and code...

              ======================================================

              Sales:

              Load *, Customer&Product as Key Inline

              [

                Customer, Product, Sales

                Cust 1, Prod 1, 2

                Cust 1, Prod 2, 5

                Cust 1, Prod 3, 4

                Cust 1, Prod 4, 6

                Cust 2, Prod 3, 7

                Cust 2, Prod 2, 8

                Cust 2, Prod 5, 7

                Cust 3, Prod 1, 6

                Cust 3, Prod 2, 5

                Cust 3, Prod 3, 3

                Cust 3, Prod 4, 4

                Cust 3, Prod 5, 5

                Cust 3, Prod 7, 4

                Cust 3, Prod 8, 3

                Cust 3, Prod 9, 6

                Cust 3, Prod 10, 5

                Cust 4, Prod 6, 4

              ];

               

               

              NoConcatenate

              Temp:

              Load Distinct Product Resident Sales;

              Join

              Load Distinct Customer Resident Sales;

               

               

              NoConcatenate

              Temp2:

              Load * Resident Sales;

              Concatenate

              Load * Where Not Exists (Key);

              Load Customer, Product, Customer&Product as Key Resident Temp;

               

               

              Left Join (Temp2)

              Load Product, Min(Sales) as ProductMinPrice Resident Sales Group By Product;

               

               

              Drop Table Temp;

              Drop Table Sales;

               

               

              NoConcatenate

              Final:

              Load

                Customer,

                Product,

                Key,

                IF(IsNull(Sales), 1,0) as Flag,

                IF(Product = Previous(Product) and IsNull(Sales), Previous(ProductMinPrice), ProductMinPrice) as ProductMinPrice,

                Sales

              Resident Temp2

              Order By Product, ProductMinPrice;

               

               

              Drop Table Temp2;

               

              ========================

              Now create a straight table

               

              Dimension

              Product

               

              Expression 1

              Label  = Sales

              Expression = SUM(Sales)

               

              Expression 2

              Label = Qty Product

              Expression = COUNT({<Flag = {0}>}DISTINCT Product)

               

              Expression 3

              Label = Remaining Opportunity

              Expression = SUM({<Flag = {1}>}ProductMinPrice)

              • Re: Opportunity view
                Marco Wedel

                Hi Diego,

                 

                just another solution:

                 

                QlikCommunity_Thread_112468_Pic1.JPG.jpg

                 

                QlikCommunity_Thread_112468_Pic2.JPG.jpg

                 

                tabSales:
                LOAD Customer,
                    Product,
                    Sales
                FROM
                [http://community.qlik.com/thread/112468]
                (html, codepage is 1252, embedded labels, table is @1);
                
                tabCustomOport:
                LOAD
                  Customer,
                  Sum(Sales) as SumSales,
                  Count(Product) as [Qty Products]
                Resident tabSales
                Group By Customer
                Order By Customer;
                
                tabProductMinSales:
                LOAD
                  Product,
                  Min(Sales) as MinSales
                Resident tabSales
                Group By Product
                Order By Product;
                
                tabOportunities:
                LOAD Distinct
                  Customer
                Resident tabSales;
                
                Join (tabOportunities)
                LOAD
                  Product,
                  MinSales
                Resident tabProductMinSales;
                
                Left Join (tabOportunities)
                LOAD
                  Customer,
                  Product,
                  Sum(Sales) as Sales
                Resident tabSales
                Group By Customer, Product;
                
                Left Join (tabCustomOport)
                LOAD
                  Customer,
                  sum(MinSales) as [Remaining Opportunity]
                Resident tabOportunities
                Where not IsNum(Sales)
                Group By Customer;
                
                DROP Table tabOportunities;
                

                 

                I am getting a different value for Remaining Opportunity Cust 4.

                Is that correct?

                 

                hope this helps

                 

                regards

                 

                Marco

                • Re: Opportunity view
                  Manish Kachhia

                  Glad that my code helps you.

                  Can you close this thread by selecting correct or helpful answer?

                    • Re: Opportunity view

                      Lol I’m trying to find out the button to close but I can’t find it!!

                       

                      I’ll reload the forum page.

                       

                      Diego Esteban Adum

                      APLA BI Manager

                       

                      "PRIVACY NOTICE: In accordance with the provisions within the Mexican Federal Data Protection Law (LFPDPPP), Ecolab S. de R. L. de C.V. ("Ecolab"), a duly incorporated enterprise under the laws of Mexico, is responsible for the processing of personal data. We collect personal information for administrative purposes related to the performance of contractual and / or commercial obligations and to fulfill the obligations of applicable law, to register you as a customer or supplier, or for recruitment purposes. All the personal data that you freely and voluntarily provide or come through this source is subject to the provisions of the Privacy Notice, which is available at the website: http://www.ecolab.com/privacy-policy”

                    • Re: Opportunity view
                      Manish Kachhia

                      It should be under all reply you receive for your questions...

                       

                      Correct or Helpful Answer.bmp

                        • Re: Opportunity view

                          Yes, I looking in that area but the only available options are Reply or Like!

                           

                          I tried with IE 8 and Chrome! Same result in both!

                           

                          I’ll reboot my computer and try again!

                           

                          Thanks!

                           

                          Diego Esteban Adum

                          APLA BI Manager

                           

                          "PRIVACY NOTICE: In accordance with the provisions within the Mexican Federal Data Protection Law (LFPDPPP), Ecolab S. de R. L. de C.V. ("Ecolab"), a duly incorporated enterprise under the laws of Mexico, is responsible for the processing of personal data. We collect personal information for administrative purposes related to the performance of contractual and / or commercial obligations and to fulfill the obligations of applicable law, to register you as a customer or supplier, or for recruitment purposes. All the personal data that you freely and voluntarily provide or come through this source is subject to the provisions of the Privacy Notice, which is available at the website: http://www.ecolab.com/privacy-policy”