Monthly distribution percentage

[#1]

Hi all!

Please help me! I have a table like this:

YearMonth	Customer	Product	Distribution
201103	A	X	0
201103	B	X	1
201103	C	X	1
201104	A	X	1
201104	C	X	1
201104	D	X	1
201105	A	X	0
201105	C	X	1

It contains many customers, many products and distribution. Distribution contains 0 or 1. 1 if the product presents at the customer.

I would like to create a bar chart, and calculate distribution percentage by product by YearMonth.

In sql like this:

select YearMonth, Product, sum(Distribution) / count(Customer)

from

group by Yearmonth, Product

How can I create this in QlikView?

Thanks a lot!

Waryou

[francisvandergr]

With Bar chart you can choose on the expressions Tab the option Relative. This option gives you a percentage.

[#3]

Hi,

When QlikView count the distribution percentage, collects all Customers. This is wrong, because there are different customers in different months.

In SQL group by handle this. But how can I do this in QlikView?

[Michiel_QV_Fan]

Check the aggr function.

[#5]

Expression is:

count({<Distr= {'>0'} >} DISTINCT  Customer)  / count(DISTINCT Customer_ID )

How can I count customers monthly, if the table contains several month and each month has different customers?

[Michiel_QV_Fan · Accepted solution]

Untested:

aggr(count(DISTINCT customer), month) / aggr(count(DISTINCT Customer_ID), month)

Count does work but try adding a calculation field with a numeric value and use sum instead. A Qlikview best practice.

[#7]

Thank You! It seems aggr() function is working.