topic Using a filter can negate "show empty groups" - is this intended product functionality? in Dashboards & Analytics Discussions

Using a filter can negate "show empty groups" - is this intended product functionality?

RADSr — Fri, 27 Jan 2023 05:38:34 GMT

It the online store e.g. below I report on customer sales.

Customer 13 has no sales but when I select "show empty groups" I get to see customer 13 - huzzah! That's what I want to happen!

But when I filter for a specific order date.... no more customer 13 - boo! That's not what I want at all!

Update: Same behavior when I filter via a dashboard prompt.

I don't see "show empty groups" in the online documentation ( nor "full outer join" ) and so I turn to you, Community, is this a product limitation? A bug? A very simple misunderstanding of which I will be embarrassed in the morning?

I miss the SQL Joins lab feature 😞

Re: Using a filter can negate "show empty groups" - is this intended product functionality?

RADSr — Fri, 27 Jan 2023 15:20:01 GMT

An interesting ( to me 😉 ) update.

The addition of a date filter seems to negate "show empty groups" but the addition of a date filter **on the measure** does not. Note that customers 10, 11, and 12 properly show no sales on the filtered date and customer 13 with no sales at all also shows up in the list.

Re: Using a filter can negate "show empty groups" - is this intended product functionality?

dylanwan — Fri, 27 Jan 2023 18:31:23 GMT

This sounds an expected behavior for me since the filter was applied on top the result of "Show Empty Group'.

Can you try if is shown by applying the criteria as the below:

OR(OnlineStore.salesorderheader.OrderDate = date('2011-10-22'), 
   IsNull(OnlineStore.salesorderheader.OrderDate)
)

Re: Using a filter can negate "show empty groups" - is this intended product functionality?

RADSr — Sat, 28 Jan 2023 19:44:17 GMT

First and foremost: The filter @dylanwan suggested is working appropriately - customer 13 is in the list.

It does produce, er, "interesting" output as the insight gets more complex.

--------------

I added a field - Customer Rating - to the customer table and added that to my insight. It is *only* showing up for the "outer" customers.

I know Incorta "knows" ( or can know ) the rating for all the customers however, because when I multiply Rating * Unitprice I get results for all customers who have a Unitprice. ( I don't expect a result for customer 13 because 45 * NULL = NULL ) ***

The upshot of all this seems to be that "show empty rows" is a nice feature, but needs to be used with the caution that individual filters apply after the result set is generated ( or before the "outer" logic is applied? ) so:

1) use w/ caution

2) add formula filters where the "outer" should still apply

3) test, test, and test especially when including any measure values coming from the "outer" table.

4) ancillary, but I had no idea the online store unit prices went out up to four decimal places!

*** If the generated SQL is to be believed .... well I don't know because I'm getting an error trying to generate the SQL 😉