How to collect data for the last day of the heiarchy month?

[lartra]

I have a report that needs to show the number of issues in a limited list of statuses on the last day of the selected month.

I’ve been able to create a tuple that ignores the Page Time filter like this:

([Status].[In Progress],[Time].DefaultMember)

That let me create a calculated member that shows all the tickets open “right now” ignoring the Page Time range selected like this:

Aggregate({
([Status].[In Progress],[Time].DefaultMember),
([Status].[Ready for Review],[Time].DefaultMember),
([Status].[Awaiting IT Completion of Security Controls & Activities],[Time].DefaultMember),
([Status].[Awaiting Questionnaire from IT],[Time].DefaultMember),
([Status].[Awaiting Information/Documentation from IT],[Time].DefaultMember)
})

But, I need to not ignore the Time but instead show the number of issues in the given status at the last moment of the month for the range selected.

Meaning if Q1 is selected count all the tickets in the above list of status at 11:59 PM March 31st.

This seems to be close:

Aggregate({
([Status].[In Progress],[Time].CurrentHierarchyMember.LastSibling),
([Status].[Ready for Review],[Time].CurrentHierarchyMember.LastSibling),
([Status].[Awaiting IT Completion of Security Controls & Activities],[Time].CurrentHierarchyMember.LastSibling),
([Status].[Awaiting Questionnaire from IT],[Time].CurrentHierarchyMember.LastSibling),
([Status].[Awaiting Information/Documentation from IT],[Time].CurrentHierarchyMember.LastSibling)
})

Except that I’m getting back a very small number. If I set it to “all time” then that should match the measure ignoring the time page filter. But, instead of 100 the above shows 8 total tickets.

Any suggestions?

Thanks!

• Leeland

[martins.vanags]

Hi,

Please check the measure “Issues history”
https://docs.eazybi.com/eazybijira/data-import/jira-issues-import/import-issue-change-history#Importissuechangehistory-Calculatedmeasures
It would count the number of issues at the end of the selected time period
And you can use it with “Transition Status” dimension members (also it works with aggregate-calculated members in the same way). Transition status dimension has historical statuses that you need in your report if you look at statuses in time periods.
You can use this formula to create a calculated member in “Transition Status” dimension using aggregate:

Aggregate({
[Transition Status].[In Progress],
[Transition Status].[Ready for Review],
[Transition Status].[Awaiting IT Completion of Security Controls & Activities],
[Transition Status].[Awaiting Questionnaire from IT],
[Transition Status].[Awaiting Information/Documentation from IT]
})

Then you can use “Time” as rows and page filter in your report and it will calculate the number of issues at the last date of the period in one of the statuses defined in the “Aggregate”

Martins / eazyBI

[lartra]

Hello @martins.vanags

Thank you for the references. That example report is incredible and I’d love to figure out how it combines bar and line display types with separate axes for everything. But, that is for the future.

@martins.vanags

Please check the measure “Issues history”
Import issue change history
It would count the number of issues at the end of the selected time period
And you can use it with “Transition Status” dimension members (also it works with aggregate-calculated members in the same way)

I have made a testing analysis (report) replicating what you showed.

That is good for just one column. But, I need to have several columns each showing a different mix of statuses.

I’ve tried a lot of different combinations of FILTER, SUM, and COUNT where I’ve made report-specific calculated Measures attempting to combine [Issues history] with an Aggregate(). I either get empty fields or error messages trying to create the calculated measure.

What I need is a set of custom-calculated measures that produce counts of issues in the specific grouping of statuses at the end of the selected time period.

Based on this information I don’t see why this isn’t working:

Issues in Active State:

-- Copied from 'Issues history' to gather the historic data
Cache(
  NonZero(Sum(PreviousPeriods([Time].CurrentHierarchyMember),
    Cache([Measures].[Transitions to]
        - [Measures].[Transitions from])
  ))
  + [Measures].[Transitions to]
  - [Measures].[Transitions from]
)
-- Now count issues in needed statuses 
Aggregate({
[Transition Status].[In Progress],
[Transition Status].[Ready for Review],
[Transition Status].[Awaiting IT Completion of Security Controls & Activities],
[Transition Status].[Awaiting Questionnaire from IT],
[Transition Status].[Awaiting Information/Documentation from IT]
})

Thanks.

[martins.vanags]

Here you would use a different formula to calculate the "Issues history in Active status"

Aggregate({
[Transition Status].[In Progress],
[Transition Status].[Ready for Review],
[Transition Status].[Awaiting IT Completion of Security Controls & Activities],
[Transition Status].[Awaiting Questionnaire from IT],
[Transition Status].[Awaiting Information/Documentation from IT]
},
[Measures].[Issues history]
)

Martins / eazyBI support

[lartra]

Thank you, but that seems to give very large values. Comparing this with another formula that appears to give “close to” results I get very different values.

[Measures].[Test Hours in via tuples]  =

 (
  [Measures].[Days in transition status],
  [Issue Type].[Advisement],
  [Transition Status].[Ready for Review]
 )
 +
 (
  [Measures].[Days in transition status],
  [Issue Type].[Advisement],
  [Transition Status].[In Progress]
 )

Formatting: #,###.## h

AND

[Measures].[Test Hours in via Aggregate]  =

Aggregate({
[Transition Status].[Ready for Review],
[Transition Status].[In Progress]
},
[Measures].[Days in transition status]
)

Formatting: #,###.## h

Produces:

[lartra]

I realized that the second was not also limiting the results to Issues of type Advisement.

Now if I change the calculated measure to this:

Aggregate(
 {
  [Transition Status].[Ready for Review],
  [Transition Status].[In Progress]
 },
 (
  [Measures].[Days in transition status],
  [Issue Type].[Advisement]
 )
)

Formatting: #,###.## h

That produces the exact same answer. This makes me feel more confident in my addition method.

Is one more efficiency than the other for the EazyBI engine?

[martins.vanags]

@lartra

Both approaches are tuples and would be equally efficient from the query perspective.
I would prefer the last method because the formula is shorter, but I could believe that first method is easier to learn and understand the principle of tuples.

Martins / eazyBI