Skip to main content
cancel
Showing results for
Search instead for
Did you mean:
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
MVP

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;``````

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

6 Replies
MVP

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;``````

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

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

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

@Jagveer  try below expression

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

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

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

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

Community Browser