Count instead of Sum

The default behavior of board board cube is the sum data by the dimensions used in a Data View. For example the total amount sold per month per product.

In the example below I used the cube QTY Sold on my rows I selected the product category and on my columns I selected the months.

Counting customers

Sometimes you want to know the number of customers instead of the total quantity per customer per month. 

In Board 10 you had to define a special cube for this, a so called MXC cube and the disadvantage of an MXC cube as that you could only use fill the cube with a data reader and not with a procedure.

In Board 11 it is now possible to use the aggregate function and do a distinct count. In your data view layout you use the cube QTY Sold and on the right you select the function AGGREGATION, here you select the option Distinct Count and On Entity you select the entity Customer. 

In this example Board will now count the number of unique customers per category and month.

The result of the data view.

Now you might think that the total in data view below is wrong, but it is not. Because the same customer can buy an item in the category Books and electronics, so inside the category it is an unique customer but in the total these two purchases should be counted as 1 customer.

So as you can see in the example above in January 1.969 customers bought an item in the category Books.

You could check this by building a data view where you have your category and customer on your rows and use a formula to count each customer if they there is a QTY SOLD for this customer in a period.

In the example below you see that I used a simple formula (Counter), where I am saying if QTY Sold >=1 then Counter = 1. This way you can count the number of customers.

When I collapse the data view then you will see next tho the QTY Sold the Counter and this number is equal to the number in the data view below with the distinct count.