Handling Latest Records in widget (pivot/charts) in context of filters selected

 

Hi,

I have a table with test cases (testcase_key, Type), cycles (cycle1, cycle2, ...), and executions (test_result, execution_timestamp).

Scenario: A test can be executed multiple times within one cycle, and the same test case can also be executed in subsequent cycles. I want to keep only the latest execution of each test case in a pivot table. The dashboard has a filter for cycles.

I can write custom SQL like this:

SELECT *
FROM (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY testcase_key ORDER BY execution_timestamp DESC) as rn
FROM executions
) sub
WHERE rn = 1;

The challenge with this approach is that it only shows the test case of the latest cycle. If a user selects any past cycle, I am not able to see the latest test case status and type for that cycle.

I have also tried using JAQL, but it doesn't work as expected. Here is the JAQL query I used:

{
"top": 1,
"by": {
"table": "executions",
"column": "actualenddate_ts",
"dim": "[executions.actualenddate_ts]",
"datatype": "numeric",
"agg": "max"
},
"context": {
"partition": [
{
"table": "executions",
"column": "testcase_key",
"dim": "[executions.testcase_key]",
"datatype": "text"
},
{
"table": "executions",
"column": "issue_key",
"dim": "[executions.issue_key]",
"datatype": "text"
}
],
"sort": {
"table": "executions",
"column": "actualenddate_ts",
"dim": "[executions.actualenddate_ts]",
"datatype": "numeric",
"order": "desc"
}
}
}