I have a question about relationships between tables: In a 1:N relationship, how do I calculate the number of entities on the left side w/o a relationship. I.e. the 1:0 'links'?

Hi @Daniel Wakefield Let me offer some options. I will use as example datasets products with primary key product_id and orders with primary key order_id and foreign key product_id. Following metrics should return number of products without orders: 1.

select count({attribute/product_id},{dataset/products}) - count({attribute/product_id},{dataset/orders})

- number of unique primary keys in dataset “products” minus number of unique foreign keys in dataset “orders” 2.

select count({attribute/product_id}) where (select count({attribute/order_id}) by {attribute/product_id} for each {attribute/product_id})=NULL

- calculate number of orders for each product (products without orders will be represented by NULL value because of applied outer join using FOR EACH keyword) and use it to filter the counted products.

This seems like it's mostly working. However, in my data, both products and orders have date columns, and the date filter for the dashboard isn't properly filtering this calculation.

So the both datasets are connected to the same date dataset in your model?

No.

In the widget configuration there is one of the date datasets selected and the dashboard filter is applied only on one of them.

Where is the 'widget configuration'?

If you click on insight on dashboard in edit mode, tgere is menu with "Configuration" option. It just explains why it does not work.

Oh. So how do I get it to work then?

You cannot select both.

Okay, so is it possible to do this with GoodData at all?

1. you may try ignore warning about alternative paths if it is just warning 2. You may try to transform your data structures to star/snowflake schema without loops if possible 3. You may try some complex solution with "date arithmetics"

Well, it is star/snowflake currently, right? I mean, that's why I had to remove the date relation

Option 3: you may add disconnected dataset which contains all dates and use formula

(select min({label/disconnected_date.day},{dataset/disconnected_dataset}) by all other)

with dashboard date filter applied as range of days of disconnected date dataset.

Okay. So how would I apply this to the original MAQL query

select count({attribute/product_id}) where (select count({attribute/order_id}) by {attribute/product_id} for each {attribute/product_id})=NULL

?

Then you can use results of these sub-metrics in filter conditions for the teo date datasets

select count({attribute/product_id}) where {label/product_date.day} between {metric/start} and {metric/end} and  (select count({attribute/order_id}) by {attribute/product_id} for each {attribute/product_id} where {label/order_date.day} between {metric/start} and {metric/end})=null

So, I have to put all of the order and product dates into a new table, unconnected to everything else?

Yes. This is the suggestion. Maybe there exists a solution in which you use some existing dataset which contains all required dates or where missing dates are filled using FOR EACH keyword, but I fear it would be even more complex

How do I create this disconnected dataset?

If you add your SQL as definition of SQL dataset this SQL will be used by query engine as source of data

So, I have to modify my physical data model?

You can add it to logical model

How do you do that?

https://www.gooddata.com/developers/cloud-native/doc/cloud/model-data/create-logical-data-model/create-sql-datasets/ please note that this feature is currently in beta