timeDimension based off of multiple columns

Data Schema
#1

I have a possibly weird use case that I’ve been trying to figure out how to implement. I would like to create a concept of ‘open’ on one of my tables that I could use to aggregate data by. However, determining this status requires comparing two columns since I want to be able to look back in time to find the rows that could be considered ‘active’ in a previous time range.
eg:

cube('Order', {
  sql: `select * from order`,
  measures: {
    amountSum: {
      sql: `amount`,
      type: `sum`,
    },
  },
  dimensions: {
    started: {
      sql: `started`,
      type: `time`,
    },
    shipped: {
      sql: `shipped`,
      type: `time`,
    },
    open: {
      sql: `started <= $1 AND $2 <= shipped`,
      type: `time`,
    },
  },
});

Obviously this above example doesn’t work, but hopefully it illustrates what I’m looking for.
I’ve tried using the FILTER_PARAMS to try defining some custom behaviour for this but unfortunately it seems like the way FILTER_PARAMS are applied doesn’t allow this:

cube('Order', {
...
open: {
  sql: `FILTER_PARAMS.Order.open.filter((start, end) => {
    return `started <= ${start} AND ${end} <= shipped`;
  }

because the above gets translated to:

SELECT amount_sum
FROM order
WHERE (
  (started <= ${start} AND ${end} <= shipped) > ${start}
) AND (
  (started <= ${start} AND ${end} <= shipped) < ${end}
);

So either this isn’t how the FILTER_PARAMS are supposed to be used, or there is a bug in how they are applied. If the former, is there any way I can achieve this behaviour of creating a timeDimension based off two columns? If the latter I’d be interested in trying to get this fixed since this would be really useful for me…

#2

Hi @tchell and thanks for your question!
Let’s get away from the implementation for now and focus on what exactly do you want to achieve here. I want to know how exactly you can check that order is ‘open’?
Do you have separate rows with time for each order when it was started and shipped and do you want to use that data to check if there are any started ones without shipped data?

#3

Hey thanks for the response Ivan.

Yeah no problem. To check when a single order is open you’d check when a row has a started date that is less than your query time, and a shipped date that is greater than the query time. So if you were going to ask “What orders were open on 2022-01-01?” you’d query:

SELECT *
FROM order
WHERE started <= '2022-01-01' AND '2022-01-01' <= shipped;

So each row has the started and shipped columns. And yes for orders that are still open, shipped would be NULL so we’d have to add a check for that. But I think it would be sufficient to do:

WHERE started <= '2022-01-01' 
AND (shipped IS NULL OR '2022-01-01' <= shipped ))

Since if the started <= '2022-01-01' is true, then the order is open for any value provided to query the shipped date, assuming the started_query_value <= shipped_query_value.

Does that answer your questions?