#gd-questions Hi guys! I need a help for the following problem: i have 2 fact tables, the first one is sales data and the second one is stock quantity data. they are related to different date dimensions, How can i make a chart of quantity sold and stock by each day if the Date dimensions are not the same?

Hi Felipe. Unfortunately in this situation you can not put these two metrics into a single chart if they do not share the same date dimension. GoodData currently does not support this. If it is possible, Ibwould recommend to change the data model so that you have a shared date dimension for both fact tables. Either connect one of the existing ones to the other table or add a brand new date dimension and connect it to both fact tables.

Thx for the answer, in case i want to create a new date dimension that relates to both facts, how can i explicit tell what date should relate to what?

In the data it will work similarly as any other shared dimension (i.e. product or customer). So each of your fact tables will contain one date which is connected to this shared date. Which date it is depends on to what date you map it. And you should map it to the date by which you want to compare your data. In the sales data it will be probably the sales date while in the stock quantity it will be the date to which the stock quantity is captured. (I do not know your exact data and model ao I am just guessing). And then if you use this "common date" in your insight, for metrics regarding sales data it will represent the sales date and for metrics regarding the stock data it will be the date of stock at eacj day. If you have more different date dimensions in each of your fact tables (i.e. order date, delivery date, invoice due date...) you will need to pick only one of them for the shared dimension (and can keep the other dates as separate date dimensions, which will not be shared).

Hi @Felipe Porto, I agree that it would be good to model it in a way that facts have a common dimension as suggested by @Michal Hauzírek to be able to create flexible insights based on facts from different fact tables. There exists also workaround using MAQL, if it is needed just for one specific fixed visualization. Example: I want to plot two metrics M1 and M2 on common axis showing data for last 6 months. M1 relates only to Date 1 and M2 relates only to Date 2. I put M1 with Date 1 into insight, but I cannot add M2 directly. I need to wrap M2 by following MAQL expression:

select case 
 when Month/Year (Date 1)=this-5 then ifnull((select M2 by all other where Month/Year (Date 2)=this-5), 0),
 when Month/Year (Date 1)=this-4 then ifnull((select M2 by all other where Month/Year (Date 2)=this-4), 0),
 when Month/Year (Date 1)=this-3 then ifnull((select M2 by all other where Month/Year (Date 2)=this-3), 0),
 when Month/Year (Date 1)=this-2 then ifnull((select M2 by all other where Month/Year (Date 2)=this-2), 0),
 when Month/Year (Date 1)=this-1 then ifnull((select M2 by all other where Month/Year (Date 2)=this-1), 0),
 when Month/Year (Date 1)=this then ifnull((select M2 by all other where Month/Year (Date 2)=this), 0) 
 end

The trick is I need to calculate value for specific data point as a one number which is not sliced by Date 2, e.g. using following statement for last month

(select M2 by all other where Month/Year (Date 2)=this-1)

where condition filters last month and :“by all other” tells that the metric will be not sliced by Date 2 so result is just one number which can be be repeated for each value of Date 1 dimension. The case statement will let me join tables with such scalars sliced by Date 1 and select appropriate scalar to create a vector of last 6 months. It may be needed to use ifnull function if there is a period in which M2 does not have any data. It is quite complex workaround which comes with some limitations (you cannot drill by attribute or let user adjust the date filter etc.), but it can be handy if you need this just for only one specific visualization.

Thx a lot guys! Helped me a lot