Solved

How do you count records with effective dates

  • 20 February 2023
  • 1 reply
  • 68 views

If I have a `users` table with effective dates, so the columns like this:

id, start_date, end_date

And I want to count the users that were active anytime in a filtered date range in sql I would just do

select count(distinct(id)) from users where start_date <= {end of range} and end_date > {start of range}

The values {end of range} and {start of range} would be based on whatever date filter the user selected in the dashboard - for example if they selected YTD {start of range} would be 2023-01-01 and end of range would be 2023-02-20

I can’t figure out how to do this in the metrics editor though. Is it possible?

icon

Best answer by Moises Morales 20 February 2023, 20:08

View original

1 reply

Userlevel 3

Hi Scott, 

 

I believe this will depend on how your LDM is structured. If the dates are loaded as facts, you could simply calcuate the difference between them, i.e.,  start_date - end_date. I can also recommend checking this documentation on how to get the duration of an event: Date Attribute Arithmetic | GoodData Free.  If you have a MAQL metric defined, feel free to send it over here so we can help you troubleshoot it.

Reply