Hello. A question about MAQL and Ranking filters. As far as I understand, ranking filters like TOP(n) can be used to filter based on the fact/metric value. However, I need to use similar logic, but applied to a Date attribute. Basically I need to find the record with latest date value. Currently I have the query:

SELECT COUNT (
	Date (NPS Date),
	Records of NPS History
)
WHERE Has_Responded = Yes

This finds me NPS surveys that have been sent to the client and received a response. The dataset also has the client email attribute attached. So I would like to find the latest NPS survey that has been sent to the client and for which a a response has been received.

Hello Allan. The MAX() and MIN() functions can be used to calculate the latest or earliest values of date attributes. I would recommend having a look at https://community.gooddata.com/metrics-and-maql-57/how-can-i-custom-a-attribute-or-metric-in-date-format-227?postid=1471#post1471 and https://help.gooddata.com/doc/enterprise/en/dashboards-and-insights/manage-custom-date-dimensions in case you want more detail on what sort of behaviour can be expected from the platform.

Hi @Jan Rehanek! Thanks for your answer. It worked and I was able to find the latest responded survey per client. However, I need to extend the metric and the reports a bit further and I am struggling with a more complex version of the query. Perhaps you have any ideas for this as well. The query now looks like this:

SELECT MAX (
  SELECT (
  	1000000 * MAX(Day of Month (NPS Date)) +
    10000 * MAX(Month (NPS Date)) +
    MAX(Year (NPS Date)) -
    1900 + 1900
  )
  BY Records of NPS History
)
WHERE 
	Is_Responded = Yes
    AND (
      SELECT COUNT (
          Date (NPS Date),
          Records of NPS History
      )
      WHERE Is_Responded = No
      WITH PARENT FILTER
  	) > 0
WITH PARENT FILTER EXCEPT Date (NPS Date)

What I want to achieve is a metric that comes back with the date of the most recent survey that has received a response for a client who has an unanswered survey within the filtered time period. For instance, the user selects time period ‘last month’ and sees, which clients have not yet responded the survey and when was the last time we sent a survey and did receive a response from the same person. The problem with that metric currently is that the “WITH PARENT FILTER EXCEPT Date (NPS Date)” section doesn’t appear to have any effect. So it only shows a value if that previously unanswered survey happens to fall into the same time period that is selected in the filter. It should show a result even if the previous survey was sent in the past time period.

Thanks for the update, Allan. I’ll also take a look at this and let you know if I have some tips which could help you achieve the results you want.

Hi @Jan Rehanek! I have continued on this and I feel like I am having progress, but I am still facing an issue which I don’t seem to be able to figure out. Something going wrong with parent filters. So, I have a query:

SELECT MAX (
  SELECT (
    10000 * MAX(Year (Due Date))) 
    + (100 * MAX(Month (Due Date))) 
    + (MAX(Day of Month (Due Date)))
    BY Records of NPS History
  )
WHERE (
  Is_Responded = Yes
  AND IFNULL (
    (
      SELECT COUNT (Date (Worked Date), Records of NPS History)
      WHERE Is_Responded = No
      WITH PF
    ), 0 
  ) > 0
)
WITH PF EXCEPT Date (Worked Date)

The overall goal is still the same as I explained before. This metric should show the expiry date (Due Date) of latest answered survey (regardless of the time period of that survey/answer) for a client contact IF there is an unanswered survey in the filtered time period. Filtering happens on “Worked Date” which is used to record the date when the survey was sent. See the screenshot for illustration. The metric definition is for “Prev. Expiry”. “Prev. Sent” is the sending date of that previous survey whose expiration date is shown in “Prev. Expiry”. Possible “Prev Expiry” and “Prev Sent” values should not be affected, by the report/dashboard filter on the “Worked Date” column, but there should only be a value when there is an unanswered survey in the selected time period (if there is a value in the “New Sent” column).

Hi Allan, Thank you for all of these provided details here. These metric issues can be a bit difficult to troubleshoot without access to the workspace and looking into everything ourselves. However, while going through our records, we’ve noticed that you are direct customer of Bootiq. Bootiq may be able to offer you a better solution for your reporting if this is related to their custom solution. If this is not related to Bootiq, and this is your own custom workspace/solution. We would like to invite you to open a support ticket with us, by emailing us at support@gooddata.com. Please provide a direct link to the report in question and all details, and we will take a look if possible.

Hi @Joseph Heun Not sure what you mean by Bootiq’s custom solution - while we are on Bootiq’s domain, the workspace setup is completely our own. Would then support from GD be still possible if we grant you access to the workspace?

Yes, could you please open a support ticket with us and provide access to the workspace?