topic Re: Limitations Search and insights engine? in Alfresco Forum

Limitations Search and insights engine?

hansdebal — Mon, 14 Oct 2019 13:05:04 GMT

Hi,

I think I'm stumbling upon some limitations of the insights engine. Is it somehow possible to:

* Execute a query with date-difference queries? Eg: I want to be able to create a query (in Zeppelin/Solr) which calculates how many days a certain document is active (suppose we have a custom aspect which defines an inactiveDate). So I want a query which calculates inActiveDate - cm:created and then groups the results accordong this datediff. so my result should be:

- There were x documents active for 5 days

- there were y documents active for 7 days

- ...

Possible?

Running on ACS 6.1 and insights engine 1.1.0.1

Re: Limitations Search and insights engine?

angelborroy — Mon, 14 Oct 2019 16:35:03 GMT

DATEDIFF sql function is not supported, as this cannot be achieved using Solr SQL.

The better approach is tu use a classic facet query for this use case.

Re: Limitations Search and insights engine?

hansdebal — Tue, 15 Oct 2019 08:11:14 GMT

Is it possible to do facet queries in Zeppelin? Because I would want to put the results in a pie-chart or something similar.

Re: Limitations Search and insights engine?

angelborroy — Tue, 15 Oct 2019 08:18:22 GMT

Did you try a GROUP BY clause?

Re: Limitations Search and insights engine?

hansdebal — Tue, 15 Oct 2019 12:24:28 GMT

Yes, but to do that I would first need a way to calcalute the date-difference, no?

If this is not possible, I would think this is a feature that should really be investigated. Common use cases you would want to report on is:

* average throughput time of a case (from open to closed)

* average throughtput of workflows

* all other date-related reports

Re: Limitations Search and insights engine?

angelborroy — Tue, 15 Oct 2019 16:10:19 GMT

Yes, I also think this can be an useful feature.

Can you open an issue for that?

We'll try to include it in a future release.

Thanks!

Re: Limitations Search and insights engine?

hansdebal — Fri, 18 Oct 2019 09:16:06 GMT

Done: https://github.com/Alfresco/SearchServices/issues/210