Creating Metric for date range in GoodData

4 Comments

  • Graeme Carmichael
    Comment actions Permalink

    David

    As you suspected, you can do this through a custom metric. Your case is a little complex as your quarters begin mid month.

    This custom metric returns 1 for dates that match Q1 in 2016:

    SELECT CASE WHEN Date (Ticket Created) BETWEEN 25/01/2016 AND 28/03/2016 THEN 1 ELSE 0 END

    You would use this in a filter or on the body of the report to highlight the Q1 tickets.

    The drawback is that this only works for one year. To make your select more generic, you can use Day of Year (Ticket Created). This drops the year and counts the number of days into the year.

    So, the metric becomes:

    SELECT CASE WHEN Day of Year (Ticket Created) BETWEEN D25 AND D85 THEN 1 ELSE 0 END

    Now you only need to adjust for leap years.

    0
  • David Phillips
    Comment actions Permalink

    Hi,

    Thanks for the quick response! I've successfully created the metric as defined above. I now need to add it to a report. 

    It's probably worth explaining why we want this - I tried originally to do it though the Date filter - however, you can't select the date range here, just a floating range or select every date that you want to be included, which is a bit laborious! 

    So - I thought that there must be a better way. 

    The only option I can see to add the metric to filter an individual report is through the Numeric Range or Ranking Filter. In the example below I selected Numeric Range, and filtered by Date (Ticket Created) and the Custom Metric. I'm assuming that the CASE returned by the query should return 1 if the ticket creation date is within the date range specified by the metric. 

     

    However, as shown by the output, I get 0 tickets, when I know this should return hundreds.

    Any ideas? 

    Thanks, 

    David

     

     

    0
  • Graeme Carmichael
    Comment actions Permalink

    David

    It looks like you are on the correct lines here. That is the approach that I would recommend.

    Are you sure that you do not have other filters that preventing data returning?

    I would display your data as a table. Include the ticket ID, the ticket creation date and your new metric. Remove filters one at a time until you can see data. Check that your custom metric displays '1' against the dates you are expecting.

     

     

    0
  • Robin VanBrunt
    Comment actions Permalink

    It would be extremely helpful to have date range field options ("Start Date" / "End Date").   Most companies use date ranges, not number of days. 

    1

Please sign in to leave a comment.

Powered by Zendesk