The 3 tables are connected by a concatenaed key from the respective material + date of the transaction type (order / delivery / invoice). As you can see, the 3 fields "IDMaterial", "Material" and "Mat" are the same. They had to be renamed in order to avoid auto-key-building. Maybe this is already a flaw in my data model.
When I display the values in single charts (one per table), it is correct:
However, I want to to create ONE chart with the "generic" dimension "MonthYear" (irrespective of the transaction type) and all 3 expressions "OrderValue", "DeliveryValue" and "InvoiceValue". But I do not have this generic dimension that is associated with all 3 tables. I only have one distinct "MonthYear" field in every table. If I try to show the values over one of these fields, there naturally is a problem. For example, in the "Order" table, there are only values for 10-2017 till 12-2017. But there are a delivery and and invoice in 01-2018. As 01-2018 does not exist in the OrderMonthYear dimension values, it is "null":
My question ist: How can I create an "integrated MonthYear" dimension which can collect ALL order, delivery and invoice values?
I think you can do it a) in the data model by creating an aggregation table or b) in the layout with some expression. But I do not know how. In solution a) there would also be the problem that I would have all the values per MonthYear, yes. But then I lose the connection to the Material which also has to be applicable as a filter.
Please find attached the QVW. Thank you very much for your help in advance.
Re: Data Modeling Problem: How to show field values in a chart from 3 different tables over a "generic" dimension (e.g. time)?
IMO your datamodel
"containing the sales process from Order -> Delivery -> Invoice"
couldn't work. Because linking them over the different date-fields from the tables and the Material don't lead to a sensible datamodel. I suggest a linking over the OrderID and Material is outsourced within a dimension-table and maybe linked to the order-table. To get rid of the different date-fields isn't easy because you need something like: Canonical Date.