Calculating the sales penetration

[Jagveer]

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

[Nicole-Smith · Accepted solution]

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

[Kushal_Chawda]

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

[Jagveer]

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]

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))