It is possible to merge both of these. As I understand, you have two date dimensions, one for Invoice date and another relating to the Customer Start date. So, understandably, two fact tables to show the data from.
You may be using accumulation in the first chart making it a bit more tricky.
You could create a independent Date Dimension (Not linking with any other table in the model), and use that as the Dimension in the new chart. For the first expression, compare the Customer Start date to the new date dimension, and for the second, compare the invoice date to the new date dimension.
For example, if your first expression was Count(Distinct CustomerID) and Dimension was CustomerStartDate, You could make it like:
Count( Distinct If(CustomerStartDate<=IDate,CustomerID))
and for the second, if your expression was:
Then change it to:
and keep IMonth_Yr as your dimension in the chart.
yes, you create it as a table in the script. See the following code snippet for details and to gather ideas from.
SET vStart = 40178; // QV-internal rep. of date
date( $(vStart) + rowno()) AS RawDate
num(RawDate) AS $Date_ID,
RawDate AS Date,
Month(RawDate) AS Month,
IF(Month(RawDate) < 4, 'Q1',
IF(Month(RawDate) < 7, 'Q2',
IF(Month(RawDate) <10, 'Q3',
IF(Month(RawDate) <13, 'Q4', 'Q5')))) AS Quarter,
Year(RawDate) AS Year,
dual(Month(RawDate) & '-' & Year(RawDate), Year(RawDate) & Month(RawDate))