https://www.gooddata.com/ logo
Join Slack
Powered by
Hi all, We’re looking to use a metric that uses th...
# gooddata-cloud
t
Hi all, We’re looking to use a metric that uses the date filters chosen in a specific way. We have records in a table that have a start date column and an end date column. We want to use date filters to filter for all records that overlap with the date range supplied by the user. We’ll use the filtered records for some simple metrics (count at beginning of period, count at end of period, count of records that ended during the date range). We’ve explored two ideas: • Two date filters to supply both the start and end dates separately. This might be an issue as each date filter is expecting a range so we’d have to pass a beginning of time -> start date and end date -> end of time. • One data filter that supplies both the start and end dates to be supplied How do we get the Start and End date values supplied by the user to be able to be passed to the metrics? An example metric in pseudocode would be SELECT COUNT(table.id) WHERE table.start < filter.start AND table.end > filter.start Is this possible with MAQL?
👀 1
m
Hi Tim, please let us check internally and get back to you with more details as soon as possible
Hi Tim, thanks for bearing with me while I checked this further. As I understand it, you want to apply a date filter (chosen by the user) to count records that were active within the selected date range. Unfortunately, MAQL cannot dynamically filter records based on a user-defined date range when working with both 
start_date
and 
end_date
. MAQL is designed for analytical aggregations and does not support dynamic filtering using two user-selected dates as variables in a single metric. My apologies that I cannot provide you with a better answer at this time. But perhaps if you can provide more details on what and how you’re measuring this, we could possibly help more. In your example: 
SELECT COUNT(table.id)
. Can you please tell me if this “tableID” has statuses that you track in your data? For example the table is: Opened, Closed, etc? Perhaps you can track the ID’s by using the statuses?
t
Thanks for looking into this. Here's a sample of what the data looks like. We have a lot of non-GoodData reports that take in a start and end value as a date range. For this one, we're looking for all of those that were active during a certain time period. We could convert these data in a long format where every date has a value for that event/employee but that seems cumbersome. Is there a way to create a date filter that uses 
>
or 
<
values rather than the range values? The workaround for that is to pass in a static period and set the beginning value to as early as possible but this isn't a great user experience for our end users.
j
Hi Tim, there isn't a way to set a filter using 
>
or 
<
. Unfortunately, the static date filter would be the route to go. However, there may be a workaround to build a custom viz using SDK with those filter options. If building this on your own isn't an option, then we can certainly submit this as product feedback on your behalf.
t
Yes, I'd like to submit this as product feedback. I think we need this kind of date filtering option even if not through the UI, but allowable through YAML specification.
p
🎉 New note created.
3 Views
Open in Slack
PreviousNext
Powered by