I am trying to build an insight that shows locations that have never placed an order. For this example, I have an order fact table and a location dimension table. If I pull the location name from the location dimension it obviously shows me ALL locations. But then If I pull anything from orders, the result set decreases to just the locations that have placed an order. But I am specifically trying to figure out what locations have not placed any orders. Is there some kind of trick I can use to make a report that shows me all locations (with orders and without orders)? I essentially need to select locations with a left join to orders (if I was doing this with SQL). Or is there a trick to just show the locations with 0 orders (not the rest)?

Hi Willie, in this case you would want to utilize the IFNULL statement on your metric. This will ensure that even locations that have no orders (i.e., null values) will be displayed instead. You’d be looking to build a metric somewhat like this:

SELECT IFNULL(SUM (Orders), 0) *1

This will make it so any null values are replaced by a 0. The

*1

added at the end is a workaround we use to enforce the left JOIN, by adding a non-null value to the query. Then you can add it to the designer and slice it by locations as normal. Let me know if that’s what you’re looking for!

Awesome! It worked perfect. Thank you!