EazyBI - Filter Overdue AND not in status "Closed"

[Lars]

Hey, 

i want to set up a chart and have the following problem:

Some Issues are not resolved but in status "closed", i used this code to get all overdue issues:

nonzero(count(
Filter(Descendants([Issue].Currentmember,[Issue].[Issue]),
Datecompare(
[Measures].[Issue due date],
[Time].CurrentHierarchyMember.Startdate
)<0
and
(isempty ([Measures].[Issue resolution date])
or
Datecompare(
[Measures].[Issue resolution date],
[Time].CurrentHierarchyMember.Startdate
)>0
)
and
([Measures].[Issues created],
[Time].Currenthierarchy.DefaultMember)>0
)
))

Which works fine so far. The problem is, that it also counts the issues which are closed but not resolved. e.g. When i add the status dimension, im able to see that there are only 98 issues overdue. Without this dimension the value is 116. These 18 issues are closed but still shown by this code. I cannot simply add the status dimension, because this results in a confusing report.

Probably its possible to add code to this existing one, so that only Overdue Issues with a certain status (= not closed) are shown. The ability to hide specific values in a reports column without removing the whole member would also help.

Is there any solution for this? And additionally, is it possible to hide specific values in a chart without removing the respective member?

Thanks, 

Lars

[Martins Vanags -eazyBI-]

Hi,

Well done with the code so far.

I believe you could try updating the same code and exclude issues with the current status "closed" (as you already are saying).

Try this code instead (note the code on line3)

Nonzero(count(
Filter(Descendants([Issue].Currentmember,[Issue].[Issue]),
[Measures].[Issue status] <> "Closed" AND
Datecompare(
[Measures].[Issue due date],
[Time].CurrentHierarchyMember.Startdate
)<0
AND
(isempty ([Measures].[Issue resolution date])
OR
Datecompare(
[Measures].[Issue resolution date],
[Time].CurrentHierarchyMember.Startdate
)>0
)
AND
([Measures].[Issues created],
[Time].Currenthierarchy.DefaultMember)>0
)
))

And then you could try conditional cell formatting to hide some values (perhaps choosing text color white)

https://docs.eazybi.com/display/EAZYBI/Create+reports#Createreports-Conditionalformattingoncells

Best regards,

Martins / eazyBI team

[Lars]

Thanks Martins, works perfectly!

May I ask how to include this code in line 3 in the following codes?

Sum(
{
PreviousPeriods([Time].CurrentHierarchyMember),
[Time].CurrentHierarchyMember
},
[Measures].[Issues resolved]
)

and also in a simple formula like

[Measures].[Open issues]- [Measures].[Overdue]

Regards,

Lars

[Martins Vanags -eazyBI-]

Hi Lars,

Please try defining "Closed" status via eazyBI import settings

https://docs.eazybi.com/display/EAZYBIJIRA/Issues+closed+measure

Next, you could add line:  IsEmpty([Measures].[Issues closed]) in the calculated measure, I shared the first (I believe it is "overdue issues").

And then for your cumulative measure, you could try the following code:

Sum(
{
PreviousPeriods([Time].CurrentHierarchyMember),
[Time].CurrentHierarchyMember
},
([Measures].[Issues resolved]-[Measures].[Issues closed])
)

Instead of using "Open issues", you could try creating a new one - using a tuple code of "Transition Status" calculated member (which can be an aggregation of historical statuses except for resolved statuses) and the measure "Issues history" which is available when you import issue change history.

https://docs.eazybi.com/display/EAZYBIJIRA/Import+issue+change+history

If that approach didn't work as you expected, please reach out to eazyBI support team and provide more details and report definition, as this new expected calculation is more complicated than you think.

Best regards,

Martins / eazyBI team

[Lars]

Thank you very much