Issues with Null Values When Joining Series

Issue 1: Cannot calculate Measure – null value in Join Series

1. I create a Datamart Watcher and configure Series 1 and Series 2 using three fields in Group By: Customer ID, Customer Group and Customer Class (this field contains null value). The Measure is ∑Quantity.

2. I add Join Series using the three Dimensions above with Measure being s2∑Quantity/s1∑Quantity.

3. I click Apply Settings and check the tables and I can see that there is a value for customer ID CG-0001 in Series 1 and 2 but it returns null value in Join Series.

   Open

   

Issue 2: Detection Rule is applied to the null value

1. I continue with Watcher configured above. In the Join Series there are 154 rows.

2. In Detection Rules I applied a condition Compare Quantity is greater than -1.

   Open

   

3. I finish the Watcher setup and submit it for approval to create Actions.

4. I open the Action item. In the Detected Insight table there are 153 rows – the row with the null value has been removed.

Recommended Solution

Avoid selecting fields containing rows with null values as dimensions in the Join Series definition. A database Join will not join two rows that have null value in the same field because null != null.

Null values also cannot be compared with other values, so a filter like Quantity > -1 will always omit a row with a null value.