How to count issue whose custom fields' value(date time type) equals current date?

Questions & Answers
1

I have an custom field on issue which indicates the expected resolve date, now I’m trying to get the count issue trends in one sprint, whose expected resolved date is the same as current date. I dragged Project and Fix Version as Pages to narrow down, then Time in Rows and Measure in Columns. I tried many times, but got failed – can not get the count number…

PS: [Measures].[Issue’s dev fin date] is a new measure definition which means issue’s expected resolve date

NonZero(
Count(
Filter(Descendants([Issue].CurrentHierarchyMember, [Issue].[Issue]),
DateCompare(
[Measures].[Issue’s dev fin date],
[Time].CurrentHierarchyMember
)=0)
)
)

2

Hi Susan!

I believe the only thing missing is to actually get the StartDate of the [Time].CurrentHierarchyMember:

...
DateCompare(
[Measures].[Issue’s dev fin date],
[Time].CurrentHierarchyMember.StartDate
)=0
...

Also, if you use the Issues with dev fin date measure, you should get a total of issues with dev fin date in that date.

Lauma / support@eazybi.com

3

Dear Lauma :

Thank you so much for the reply, I got the output finally, however there are some problems. Here is the snapshoot - there are two columns, one is used to present the issue sum which transition to the “Ready to Test” status on the specific day, the other is used to present the issue sum which indicate that planned to transit to "Ready to Test " status on the specific day. I think those two columns’ sum should equals with each other. Which means, Column 1’s(transitions to status) sum equals column 2’s(Plan-transite2-Readytotest) sum. However I got total different result : one is 29, the other is 98

image
image579×624 9.94 KB

4

Hi Susan!

You can click on any of the cells and select Drill through issue to see which issues are included in the calculation, what is the issue value and what is the issue history of issues you do not think should be in the calculation. In such way you can find out if the formulas are correct or should be redefined.

If you would need further assistance with this, please send the report definition to eazyBI support so we can see the full context of the report and better understand why it is working in such a way.

Lauma / support@eazybi.com

5

Lauma,

Unfortunatelly I can not Drill through issue to see the specific issue for the second column(Plan-transite-2-Readytotest). Here post the report definition, thank you for your help in advance.

{
“cube_name”: “Issues”,
“cube_reports”: [ {
“name”: “PredictTimeTest”,
“result_view”: “table”,
“definition”: {“columns”:{“dimensions”:[{“name”:“Measures”,“selected_set”:["[Measures].[Transitions to status]","[Measures].[Plan-transite2-Readytotest]"],“members”:[]},{“name”:“Transition Status”,“selected_set”:["[Transition Status].[\u5df2\u63d0\u6d4b]"],“members”:[],“bookmarked_members”:[]}]},“rows”:{“dimensions”:[{“name”:“Time”,“selected_set”:["[Time].[Day].Members"],“members”:[],“bookmarked_members”:[]}],“nonempty_crossjoin”:true},“pages”:{“dimensions”:[{“name”:“Project”,“selected_set”:["[Project].[All Projects]"],“members”:[{“depth”:0,“name”:“All Projects”,“full_name”:"[Project].[All Projects]",“drillable”:true,“type”:“all”,“expanded”:true,“drilled_into”:false},{“depth”:1,“name”:"\u7535\u5f71\u7968\u5f00\u53d1Android",“full_name”:"[Project].[\u7535\u5f71\u7968\u5f00\u53d1Android]",“drillable”:true,“key”:“TICKETSANDROID”,“expanded”:true,“drilled_into”:false,“parent_full_name”:"[Project].[All Projects]"}],“bookmarked_members”:[],“current_page_members”:["[Project].[\u7535\u5f71\u7968\u5f00\u53d1Android]"]},{“name”:“Fix Version”,“selected_set”:["[Fix Version].[All Fix Versions]"],“members”:[{“depth”:0,“name”:“All Fix Versions”,“full_name”:"[Fix Version].[All Fix Versions]",“drillable”:true,“type”:“all”,“expanded”:true,“drilled_into”:false},{“depth”:1,“name”:"\u7535\u5f71\u7968\u5f00\u53d1Android",“full_name”:"[Fix Version].[\u7535\u5f71\u7968\u5f00\u53d1Android]",“drillable”:true,“key”:“TICKETSANDROID”,“expanded”:true,“drilled_into”:false,“parent_full_name”:"[Fix Version].[All Fix Versions]"},{“depth”:2,“name”:“Unreleased”,“full_name”:"[Fix Version].[\u7535\u5f71\u7968\u5f00\u53d1Android].[Unreleased]",“drillable”:true,“expanded”:true,“drilled_into”:false,“parent_full_name”:"[Fix Version].[\u7535\u5f71\u7968\u5f00\u53d1Android]"},{“depth”:3,“name”:"\u72ec\u7acbApp1.4.5",“full_name”:"[Fix Version].[\u7535\u5f71\u7968\u5f00\u53d1Android].[Unreleased].[\u72ec\u7acbApp1.4.5]",“url_id”:85304,“parent_key”:“TICKETSANDROID”,“parent_full_name”:"[Fix Version].[\u7535\u5f71\u7968\u5f00\u53d1Android].[Unreleased]"}],“bookmarked_members”:[],“current_page_members”:["[Fix Version].[\u7535\u5f71\u7968\u5f00\u53d1Android].[Unreleased].[\u72ec\u7acbApp1.4.5]"]}]},“options”:{},“view”:{“current”:“table”,“maximized”:false,“table”:{}}}
} ],
“calculated_members”: [{“name”:“Plan-transite2-Readytotest”,“dimension”:“Measures”,“formula”:“NonZero(\nCount(\nFilter(Descendants([Issue].CurrentHierarchyMember, [Issue].[Issue]), \nDateCompare(\n[Measures].[Issue’s dev fin date],\n[Time].CurrentHierarchyMember.StartDate\n)=0)\n)\n)”,“format_string”:"#,##0"},{“name”:“Issue’s dev fin date”,“dimension”:“Measures”,“formula”:“DateWithoutTime([Measures].[Issue \u5f00\u53d1\u5b8c\u6210\u65e5\u671f])\n”,“format_string”:""}]
}

6

Hi,

Thank you for the definition. I would suggest you use the default date measure Issues with 开发完成日期, instead of doing the calculation. This measure is created during date import as measure, see https://docs.eazybi.com/display/EAZYBIJIRA/Custom+Fields#CustomFields-Measureswithcustomdatefields, and you will also be able to drill through and see what issues are included in the calculation.

Additionally, to show it when particular Transition status is selected, you can add this measure in a tuple with default transition status:

(
  [Measures].[Issues with 开发完成日期], 
  [Transition Status].DefaultMember
)

Lauma / support@eazybi.com