Hello, I have a dataset that is something like

{ start: Date, end: Date, ... }

. I'd like to be able to return records from this dataset where either

start

or

end

is in the selected interval of some Date dataset. Is this possible? If so, how?

I think you could use a metric,

But how do you get the input date? I only see the DATE macro for today and offsets from today.

Pretty sure the "THIS(DAY)" part is the piece that comes from the dates on the insights where you use it.

The docs are pretty clear that THIS is based on the current calendar date.

Hmm, ok. I'm not sure what the interval is that you're pulling into the insight is coming from.

Users always filter by some date interval.

fair point. Something to ask with GD folks because if you do use like "Last year" what is the date used...

They filter the context for you, but this is off topic. This is all documented in MAQL docs.

ok fair enough

Thank you though for your help, Steve!

(probably in the am as they're mostly based in Europe)

This problem probably does not have easy solution. You may try to add bridge dataset of many-to-many relationship and create two records for each row of original dataset (one containing start_date and the other end_date). For example SQL dataset with SQL query:

select "id","start_date" as "date",'start' as role from interval_dataset
union all
select "id","end_date" as "date",'end' as role from interval_dataset

Filter for “Date” dataset will be then applied effectively as OR filter for start and end date

🎉 New note created.

What is the actual use case? Should it filter intervals defined by start and end date in data for which one of the borders fall into the range given by date filter on dashboard? Or do you rather want to filter intervals fully contained/overlapping with the filter date range?

Thank you @Jakub Sterba! I'll review those options in the morning. The actual use case is that I have items that are "owned" for periods of time. I'd like to be able to show who owned those items for a given interval (along w/ other info).

Other solution can also be to create dataset with snapshots for each day when item was owned and use it to apply filter via many to many relationship between “snapshot date” to the original dataset. I have recorded this thread as feedback for possible product improvement in future to better support your use case.

In my opinion, the issue illustrated in the diagram below is one of the biggest problems with how the GoodData date picker works. It works great if you have point-in-time data. For example, if you wanted to find all of the purchases within a given date range, you can easily do that. But, it doesn’t seem to work very well at all with data that has a start and an end date. If you wanted to find all marketing campaigns that were active during a given time frame… not possible. If you wanted to find all campaigns that started and ended within the given time frame… not possible. You can only ask questions about the start date or the end date, which doesn’t seem very useful.

🎉 New note created.

After posting the example in my previous message, I think something “clicked” with my understanding of how GoodData works and what you are “supposed” to do when it comes to modeling data that has a start and an end date. In the example above, where you have marketing campaigns that begin and end, modeling them like this is probably not what you want to do because of the problems discussed above…

campaign_name     start     end     ad_spend
new shoes         1/1       3/31    $1,000

This doesn’t really work with how GoodData wants you to model the data. GoodData wants either discreet standalone datapoints, like the purchase data above, or it wants continuous fluid lines that are comprised of many data points. The campaign data model above is kinda like how you would model the purchase data. Each campaign is just a single row in a table. Meaning, each campaign is just a single data point. Meaning that, even though you have a start and end date, you have to choose one or the other that is going to represent the temporal space that the campaign occupies… not ideal. So, what are the alternative options? First, you could recognize that if you start zooming out far enough, lines start to look like individual points. That’s right, we’re talking about grain and granularity. Perhaps we know that our marketing campaigns are sometimes short, maybe just a few days long, but that they always fall within a business quarter. They don’t run longer than a business quarter and they don’t span multiple business quarters. With this in mind, we know that the highest “resolution” that we can look at our campaign data and have it still appear to look like dots instead of lines, is “business quarter”…

campaign_name     business_quarter     ad_spend
new shoes         Q1                   $1,000

But there’s also a second option. Instead of making our campaigns look like dots, we could make them look like continuous fluid lines. For each campaign, maybe you have the data collected at a weekly level. Or maybe you don’t, in which case you can simply “atomize” your data by smoothly dividing it. The key with this approach though is to understand that the concept of grain/granularity is still important. Since we’ve modeled the data at a weekly level, that is the highest resolution that we can look at our campaign before we start to see gaps and holes in the line, which is a very similar problem as when we modeled our campaigns with a start and end date… two definitive points in time with one big hole in the middle.

campaign_name       week        ad_spend
new shoes           1           $85
new shoes           2           $85
new shoes           3           $85
...etc

With this approach, our campaigns start at the appearance of the first data point, they end at the last data point, and you answer questions about them by aggregating the facts from every data point that belong to them. In addition, modeling the data this way allows you to answer questions about partial date ranges, e.g “How much ad spend, across all campaigns, was there just in week 3?”

Hey @John Munson, Thanks a lot for sharing your findings with us, really appreciated. We’ve gone ahead and shared your feedback with out Product team as well. I believe, this is a great topic for the upcoming call scheduled for Monday. You should be invited alongside with Michael already.