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

[Anonymous]

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/Family	Region	Country	Video Sales	Picture Sales	GIF Sales
Company A	EMEA	UK	20	0	10
Company B	APAC	HK	30	20	10

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

[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

[Anonymous]

Thanks Michael!

[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.

[Anonymous]

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.

[Gysbert_Wassenaar · Accepted solution]

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

[Anonymous]

That worked like a treat! Thanks  a lot.