Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 
Jagveer
Contributor III
Contributor III

Calculating the sales penetration

Hi All,

I have a strange problem for which i am having a very hard time in terms of how to display in qlik.

basically i have Revenue data by country, province/state, Salesman, customer and customer type

I want to calculate the Sales penetration which is simply put (Revenue/population)

the problem comes in where different levels of granularity is selected.

for example if data is filtered on a province, then all the revenue for the customers in that province is summarized but the population should not be summarized. i.e. sum(Rev C1+ Rev C2)/Prov population

but say the data is filtered on a customer that is selling across 2 provinces then the calculation would be (Rev Prov1+ Rev Prov 2)/(pop prov1+ pop prov 2) 

the problem is how to calculate the population in the formula as revenue is summarized but population depends on the demographic that is being calculated upon.

 

i have added the sample data just to explain it better.

 

please help as i am desperate

1 Solution

Accepted Solutions
Nicole-Smith

The proper way to handle this would be to keep the Revenue and Population as separate tables in your data model/load script, which would prevent the Populations from being duplicated.

Data:
LOAD * INLINE [
Country,Salesman,Province,Customer Type,Customer,Population,Revenue 
US,S1,Alabama,National,C1,4903185,500000 
US,S1,New jersey,National,C1,8882190,1000000 
US,S1,Kansas,Distributor,C2,2913314,250000 
US,S2,Kansas,Distributor,C3,2913314,375000 
US,S2,Kansas,Retailer,C4,2913314,100000 
US,S3,Pennsylvania,Distributor,C5,12801989,1250000 
Canada,S4,Ontario,National,C6,14570000,2000000 
Canada,S4,Alberta,National,C6,4371000,1500000 
];


Revenue:
LOAD Country, Salesman, Province, [Customer Type], [Customer], Revenue
RESIDENT Data;

Population:
LOAD DISTINCT Province, Population
RESIDENT Data;

DROP TABLE Data;

Nicole-Smith_0-1607984802748.png

Nicole-Smith_1-1607984829515.png

As you can see, my numbers match what you want with a simple sum(Revenue)/sum(Population).

 

 

View solution in original post

6 Replies
Nicole-Smith

The proper way to handle this would be to keep the Revenue and Population as separate tables in your data model/load script, which would prevent the Populations from being duplicated.

Data:
LOAD * INLINE [
Country,Salesman,Province,Customer Type,Customer,Population,Revenue 
US,S1,Alabama,National,C1,4903185,500000 
US,S1,New jersey,National,C1,8882190,1000000 
US,S1,Kansas,Distributor,C2,2913314,250000 
US,S2,Kansas,Distributor,C3,2913314,375000 
US,S2,Kansas,Retailer,C4,2913314,100000 
US,S3,Pennsylvania,Distributor,C5,12801989,1250000 
Canada,S4,Ontario,National,C6,14570000,2000000 
Canada,S4,Alberta,National,C6,4371000,1500000 
];


Revenue:
LOAD Country, Salesman, Province, [Customer Type], [Customer], Revenue
RESIDENT Data;

Population:
LOAD DISTINCT Province, Population
RESIDENT Data;

DROP TABLE Data;

Nicole-Smith_0-1607984802748.png

Nicole-Smith_1-1607984829515.png

As you can see, my numbers match what you want with a simple sum(Revenue)/sum(Population).

 

 

Kushal_Chawda

@Jagveer  how you want to present the data?  I mean which chart? what will be the dimensions and measures?

Jagveer
Contributor III
Contributor III
Author

There are multiple views but for starters

Market penetration for 2 years on a stacked bar chart by customer type one bar for each year under each customer type

Kushal_Chawda

@Jagveer  try below expression

=sum(Revenue)/ sum(aggr(max(Population),Country,Province))

Jagveer
Contributor III
Contributor III
Author

Hi Kush,

Nicole's suggestion worked, but i am still not able to put the graph together. pardon me if this sounds naive but i am just learning Qlik on the go..so if you could help out in that

Kushal_Chawda

@Jagveer  you can create the Graph with Dimension Customer Type and Year and then below expression

=sum(Revenue)/ sum(aggr(max(Population),Country,Province))