Find missing order

[#1]

Hi!

I have a table with fields looks like this:

Customer (Customer number)

Active (Yes or No)

Date

Order (Ordernumber)

I have put these in a chart like the one shown below.

Date	Customer	Active	Order
2014-01-01	123456	Yes	1
2014-01-01	234567	Yes	1
2014-02-01	123456	Yes	2
2014-02-01	234567	Yes
2014-03-01	123456	Yes

What I would like to do is to have a listbox with the available dates, select a date and the customers active at this date with no orders placed (field blanked in example above) to be shown.

[morgankejerhag]

Hi Henrik,

You can have an expression that looks for the missing orders. Something like

sum(if(Order='',1,0))

Just beware that the logical test might need to be different depending on how the data is read from the source. It could be 0, blank, null or something else.

If the data set is really big it will be better to do the if-statement (without sum) in the script. Then the expression only needs to be sum(MissingOrderFlag) which will be quicker than performing a if-statement for each line in the data.

See the attached example.

Best regards,

Morgan Kejerhag

Senior Business Intelligence Consultant

Drake Communication AB, Sweden

[#3]

Thank you!

I will try this, Im a bit unsure about the source if the field of customer is blank also when the order number is empty.

Best regards!

[morgankejerhag]

Hello again,

If you have a more traditional setup with Customers in one tab and Orders in another you can do it a little bit different. See the attached example.

[MarcoWedel]

Hi,

one solution could be:

hope this helps

regards

Marco

[#6]

To get it to work with the source, first I need to see which customers are active at the specific date and then find out which of theese are missing orders.

I guess it has to be done with a join?

[morgankejerhag]

It depends on the data structure. Could you attach a file or send it to me privately?

[maxgro]

another one

[#9]

The source for the data is two tables, one with the fields customernumber and status (active yes or no), and the other table with the fields customernumber, ordernumber, date of the order.

What I want is to select a date and see which customers are active and have an order for this date, and which customers do not.

[morgankejerhag]

It is often tricky to show "what is not there".

There might be a more elegant solution to it, but attached is a version that prepares the information in the script. You could improve it by removing the synthetic key.

In the script we generate lines for each date for each customer - so now there is data selected when we click a date. For each such line we check if it should be a hit (Active, Date >= RegDate and no orderNo).