6 Replies Latest reply: Aug 17, 2017 12:38 PM by Osama Shah RSS

    Checking if customer (value) exists in all territories (fields)

    Osama Shah

      Hi Qlik wizards,

       

      I'm new-ish to Qlik and have a few questions re writing the script properly.

       

      I have  a dump of data which looks as below

       

      Customer/FamilyRegionCountryVideo SalesPicture SalesGIF Sales
      Company AEMEAUK20010
      Company BAPACHK302010

       

      At the moment, the data is very nicely in one flat table. I want to analyse
      - Customers who are buying a product from all 3 service line

      - Customers who are only buying products from 1 service line above a certain amount (e.g. why no cross- selling)

      - Analysis of countries best at selling x-lines business to customers

      - Countries reliant only on a few top customers (whale curve perhaps)

       

      I know these are a lot of questions, but would you suggest the best way of loading data for this and how I'd need to join / merge fields to get to the results?

       

      Many thanks!

      Osama


      A sanitized version of the the data is as below as an example:

       

      Data:

      LOAD

          "Family ",

          "Country Level 4 (F)",

           "Country (F)",

           "Video",

             "PIctures",

              "Gifs",

                   "Other",

        FROM xxx

        • Re: Checking if customer (value) exists in all territories (fields)
          Michael Tarallo

          Hi Osama - I saw your tweet message to me as well - let me see what I can do, I will tag hic he may reply, but also gwassenaar may jump in as well.

           

          Guys if you can gladly help out Osama, greatly appreciated.

           

          In the mean-time - I will also take a look.

           

           

          When applicable please mark the appropriate replies as CORRECT. This will help community members and Qlik Employees know which discussions have already been addressed and have a possible known solution. Please mark threads as HELPFUL if the provided solution is helpful to the problem, but does not necessarily solve the indicated problem. You can mark multiple threads as HELPFUL if you feel additional info is useful to others.

           

          Regards,

           

          Michael Tarallo (@mtarallo) | Twitter

          Qlik

          • Re: Checking if customer (value) exists in all territories (fields)
            Gysbert Wassenaar

            I think I'd remodel the data a bit:

             

            Data:

            CrossTable(Product, Sales, 3)

            LOAD

                "Family"                         as [Customer/Family],

                "Country Level 4 (F)"     as Region,    

                 "Country (F)"                 as Country,

                 "Video",

                 "Pictures",

                 "Gifs",

                 "Other",

              FROM xxx

                 ;


            Then you can use the new Product and Sales fields in expressions:


            Customers who are buying a product from all 3 service line:

            Count({<[Customer/Family]={"=count({<Product={'Video','Pictures','Gifs'}>}distinct Product)=3"}>} Distinct [Customer/Family])


            Customers who are only buying products from 1 service line above a certain amount:

            Count({<[Customer/Family]={"=count({<Product={'Video','Pictures','Gifs'}>}distinct Product)=1"},Sales={">10"}>} Distinct [Customer/Family])



            Analysis of countries best at selling x-lines business to customers

            Dimension: Country

            Measure:

            Count({<[Customer/Family]={"=count({<Product={'Video','Pictures','Gifs'}>}distinct Product)>1"}>} Distinct [Customer/Family])

            Sort the table by the measure in descending order


            Countries reliant only on a few top customers (whale curve perhaps):

            That requires a bit more time then I have availble right now. Sorry. Maybe later.