EazyBI - Filter Overdue AND not in status "Closed"

Lars February 27, 2018

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

1 answer

0 votes
Sir Mārtiņš Vanags
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
March 5, 2018

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 March 6, 2018

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

Sir Mārtiņš Vanags
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
March 8, 2018

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 March 20, 2018

Thank you very much

Suggest an answer

Log in or Sign up to answer
TAGS
AUG Leaders

Atlassian Community Events