Filter table of results by a datetime customfield

Hi,

We need to filter table of results on eazyBI by a datetime customfield. The requirement is something like this:

Issues = Issues with “Fecha_Entrega_Valoración” (customfield) within the selected period by user.

I thought use “Fecha_Entrega_Valoración” customfield as Page or Row, but I can’t use it because it can’t be a dimension.

The main idea is that the user could choose a date range by datetime customfield and the issues used for calculate table columns values are filtered by datetime customfield.

In eazyBIReport.png you can see the table. In “Filter by user”, the user must be select a date range by datetime customfield. In “Filter by datetime customfield”, these values must be calculed filtering by the issues with datetime customfield in date range selected by user.

The query column is something like this:

(Count(Filter(
[Issue].[Issue].Members,
[Measures].[Issue Estado Prioridad] = “4 - Critica” and [Measures].[Issue Tiempo Valoración] <= 172800)
) /
Count([Issue].[Issue].Members)) * 100

All issues must be filtered by “Fecha_Entrega_Valoración” (datetime customfield) selected by user like I commented before.

How could I do?

Kind regards,
Diego.

Hi Diego,

Maybe DateInPeriod() function is what you are looking for.

It will probably look like this:

(Count(Filter(
[Issue].[Issue].Members,
[Measures].[Issue Estado Prioridad] = '4 - Critica'
and [Measures].[Issue Tiempo Valoración] <= 172800
AND DateInPeriod([Issue].CurrentHierarchyMember.get('Fecha_Entrega_Valoración'), [Time].CurrentMember))
) /
Count([Issue].[Issue].Members)) * 100

How it will work is that you will be able to select different time members in page filter and it will additionally filter out those issues that are between the start and the end of that selected period.

I hope that helps.
Gvido Neilands, flex.bi

1 Like

Hi GvidoN,

That’s works!

Thank for your quick reply.

Regards,
Diego.

Hi,

a small addition to the Gvido’s advice: I would suggest including one more condition in the filter, by a real measure, so the calculation would take into account the whole report context (selected values in pages, rows, and columns), not only the Time dimension selection. Currently, only issue properties are used in filter.
I would use either “Issues created” or “Issues with Fecha_Entrega_Valoración” in a tuple with Time dimension default member (so filtering by the measures won’t take into account Time selection , as it is done by needed custom field property).

(Count(Filter(
[Issue].[Issue].Members,
[Measures].[Issue Estado Prioridad] = '4 - Critica'
AND
[Measures].[Issue Tiempo Valoración] <= 172800
AND 
DateInPeriod([Issue].CurrentHierarchyMember.get('Fecha_Entrega_Valoración'), 
[Time].CurrentHierarchyMember)
AND
([Measures].[Issues created], [Time].CurrentHierarchy.DefaultMember)>0)
) /
Count([Issue].[Issue].Members)) 
* 100

Ilze, support@eazybi.com

Hi Ilze,

Thank for your recommendation. I have included this condition but using Issues with Fecha Entrega Valoración measure.

Regards,
Diego.