Count number of parallel tickets in given time interval

Questions & Answers
1

Hi,

I am quite new to EazyBi.
Where is my mistake? - I don’t get it :slight_smile:

I would like to count parallel user Stories in a given time interval to measure the workload of a specific assignee. (In our project we don’'t use 'time spend/ worklog/ … - 1 task equals one resource,). As a report I like to use a line chart or time chart to see the parallel tasks of a specific assignee (in the best case multiple at once)

  • As page I use ‘Assignee’
  • As Row I use Time (weekly)
  • As column I created the measure below

My approach is to count all User Stories whose

  • Start Date is smaller than [Time].CurrentHierarchyMember.NextStartDate AND
  • Due Date is larger than CurrentHierarchyMember.StartDate

to catch all events where the person is ‘busy’ in the given time interval.

I receive some numbers, but they are not right. Additionally the result is super slow and a timeout occurs when using time intervalls < ~1week.
(~2000 user stories in total)

NonZero(
Sum(
Filter(
[Issue].[Issue].Members,
[Measures].[Issues created] > 0 AND
DateCompare(
[Measures].[Issue due date],
[Time].CurrentHierarchyMember.StartDate
) > 0 AND
DateCompare(
[Measures].[Issue start date],
[Time].CurrentHierarchyMember.NextStartDate
) < 0
),
[Measures].[Issues created]
)
)

Kind regards,
Fabian

2

Hi @Fabian_Mielke,
​
Welcome to the eazyBI community.!
​
One of the problems for the expression giving a wrong result is that you are using the measure “Issues created” in the filter.
​The context of the cell includes the Time dimension. Therefore, the condition on “Issues created” measure is only true for the issues created within the current Time dimension member.
​
​You might reset the Time component for issue creation by using [Time].CurrentHierarchy.DefaultMember in a tuple with “Issues created”.

--resetting Time dimension
([Time].CurrentHierarchy.DefaultMember,
 [Measures].[Issues created])

The slowness of the expression is caused by iteration through all issues in the data cube. The dataset for iteration is currently defined in the line ​
​[Issue].[Issue].Members,
​
​You might replace that part with iteration through displayed Issue level only. So when you display separate issues - you only evaluate them instead of all data cube. As well as when you select only one Project from the Issue dimension on rows or Pages - you only iterate through that project instead of all data cube.
​The new version might be as follows.
​

​DescendantsSet(
​ [Issue].CurrentHierarchyMember,
​ [Issue].[Issue]),

​
You might improve the performance even further by splitting the conditions.
The current version applies all three conditions to all issues. You might rewrite it so that the date check is only applied to the issues that comply with other conditions - report context. You might move the conditions about date relevance from the primary filter of issues into the numeric calculation for the sum.
​
​The new expression might then look as follows.
​

​Sum(
 Filter(
--dataset of issues relevant to selected project from Issues dimension
  DescendantsSet(
   [Issue].CurrentHierarchyMember,
   [Issue].[Issue]), 
--primary conditions - relevant to context with Time dimension reset   
  ([Time].CurrentHierarchy.DefaultMember,
   [Measures].[Issues created])>0
   ),
--numeric part for the sum
--applying conditions on date relevance
  CASE WHEN
   DateCompare(
    [Time].CurrentHierarchyMember.StartDate,
    [Measures].[Issue due date])<0
  AND
   DateCompare(
    [Measures].[Issue start date],
    [Time].CurrentHierarchyMember.NextStartDate)<0
   THEN
--since the relevance to the context was already checked - you can use 1 instead of Issues created
   1
   END
)

​Regards,
Oskars / support@eazyBI.com