Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
Thanks Michael!
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.
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.
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
That worked like a treat! Thanks a lot.