Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
osamashahb
New Contributor

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

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

1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

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

Perhaps like this:

Data:

LOAD

     *,

     rangesum(HasVideoSales,HasPIcturesSales,HasGifsSales,HasOtherSales) as ProdSold

     ;

LOAD

    "Family ",

    "Country Level 4 (F)",

     "Country (F)",

     "Video",

       "PIctures",

        "Gifs",

             "Other",

     if("Video"  >0,1,0) as HasVideoSales,

     if("PIctures"  >0,1,0) as HasPIcturesSales,

     if("Gifs"  >0,1,0) as HasGifsSales,

     if("Other"  >0,1,0) as HasOtherSales

  FROM xxx


talk is cheap, supply exceeds demand
6 Replies
Employee
Employee

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

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

Regards,
Mike Tarallo
Qlik
osamashahb
New Contributor

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

Thanks Michael!

MVP & Luminary
MVP & Luminary

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

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.


talk is cheap, supply exceeds demand
osamashahb
New Contributor

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

Thanks Gysbert

Can I create a field where i is a count of customers by the different products sold to them?

Such as lets say

          COUNT      ( {<Product >}      "Family GCI" )  AS ProdSold

where it will create a field call ProdSold with values from 0-4 correlating to each client to whom we have sold all 4 or just 1 product as an example? The above script which I input doesn't seem be working.

MVP & Luminary
MVP & Luminary

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

Perhaps like this:

Data:

LOAD

     *,

     rangesum(HasVideoSales,HasPIcturesSales,HasGifsSales,HasOtherSales) as ProdSold

     ;

LOAD

    "Family ",

    "Country Level 4 (F)",

     "Country (F)",

     "Video",

       "PIctures",

        "Gifs",

             "Other",

     if("Video"  >0,1,0) as HasVideoSales,

     if("PIctures"  >0,1,0) as HasPIcturesSales,

     if("Gifs"  >0,1,0) as HasGifsSales,

     if("Other"  >0,1,0) as HasOtherSales

  FROM xxx


talk is cheap, supply exceeds demand
osamashahb
New Contributor

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

That worked like a treat! Thanks  a lot.

Community Browser