Customers with no sales

[fabionogarotto]

Hello all,

I would like to create a table with the list of customers with no sales, based on the current filters (e.g. year, agent, ...).

I have the following tables:

- Customers: IdCustomer is the key field, I must consider only the customers with flActive as 1, and IdAgent based on the filter

- Invoices: list of the documents (document rows are in a separate table), each with DocumentDate and IdCustomer

 The table should list all the customers with flActive = 1, based on the current filters, that have no invoice in the selected time period.

Is it possible to create such table? Any suggestion?

Thank you very much,

Fabio

[martinpohl · Accepted solution]

create a table

add dimension from your customer table (eg name)

add an expression like

sum({<flActive={1}>}1) - Count({<flActive={1}, Number={"*"}>} Name)

every customer with flActive = 1 gets a 1.

then every customer with a Number (eg the invoice number) based on selections is substracted.

Also set under add-ons show null values to deselected.

Regards

[fabionogarotto]

Thank you,

a couple of doubts on this:

The first column is the Name, then I add another column (as measure?) with the expression you wrote. This column shows 0 if the customer has sales, 1 if it has no sales.

Rows with 0 are filtered out thanks to the add-ons flag.

But then when I use a global filter, such as Year= 2019, all the rows disappear.

When I use agentId filter, it works because it's a detail of the customer table.

I would like to have all the customers with no sales on the selected year, too.

This is the current expression (I realized I must accept flActive with values from 0 to 9)

sum({<flActive={"<10"}>}1) - count({<flActive={"<10"},Number={"*"}>}Name)

Thank you!

Fabio

[fabionogarotto]

Hello,

for future reference, you can override the year filter on the first sum with

sum({$<Year=,flActive={"<10"}>}1) - count({<flActive={"<10"},Number={"*"}>}Name).

Thank you,

Fabio