topic Calculating the sales penetration in QlikView

Calculating the sales penetration

Jagveer — Mon, 14 Dec 2020 21:20:29 GMT

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

Re: Show only Current Month Data in Charts

Nicole-Smith — Mon, 14 Dec 2020 22:27:52 GMT

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

Re: Calculating the sales penetration

Kushal_Chawda — Mon, 14 Dec 2020 22:28:13 GMT

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

Re: Calculating the sales penetration

Jagveer — Mon, 14 Dec 2020 22:40:40 GMT

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

Re: Calculating the sales penetration

Kushal_Chawda — Mon, 14 Dec 2020 23:08:38 GMT

@Jagveer try below expression

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

Re: Calculating the sales penetration

Jagveer — Tue, 15 Dec 2020 15:03:01 GMT

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

Re: Calculating the sales penetration

Kushal_Chawda — Tue, 15 Dec 2020 15:41:44 GMT

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

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