Yes, you are correct. You would like to create this calculation per issue - calculate a daily workload per issue (for any workday from start date till end date). You can calculate it with MDX calculation. However, the formula might work slow in accounts with a large number of issues. You can consider using calculated JavaScript calculations to import it as a new separate measure during import as well. I a sharing both solutions.
Both examples use default Jira fields Issue created date as a start date, Issue due date as an end date, and Original estimate as total planned amount. Any of them could be substituted with some custom fields either in MDX calculation or in JavaScirpt custom field.
MDX calculation
Here is an example of MDX calculation for this:
CASE WHEN
-- show for default time periods on workingdays only
([Time].CurrentHierarchyMember.Level.Name = "Day"
and
NOT CoalesceEmpty([Time].CurrentHierarchyMember.Get("Week day name"),"") MATCHES "Saturday|Sunday")
or [Time].CurrentHierarchyMember.Level.Name MATCHES "Year|Week|Month|Quarter"
or [Time].CurrentHierarchyMember is [Time].CurrentHierarchy.DefaultMember
THEN
NonZero(SUM(
Filter(Descendants([Issue].CurrentMember,[Issue].[Issue]),
-- check if an issue has start and end date, and it falls into selected period:
NOT IsEmpty([Measures].[Issue created date])
AND
NOT IsEmpty([Measures].[Issue due date])
AND
(DateBetween(
[Time].CurrentHierarchyMember.StartDate,
[Measures].[Issue created date],
[Measures].[Issue due date])
OR
DateInPeriod(
[Measures].[Issue created date],
[Time].CurrentHierarchyMember)
)),
-- workload per day = total planned time / workdays in planned period
-- total planned time
([Measures].[Original estimated hours],
[Time].CurrentHierarchy.DefaultMember)
/
-- workdays in planned period
DateDiffWorkdays(
DateWithoutTime([Measures].[Issue created date]),
DateAddDays(DateWithoutTime([Measures].[Issue due date]),1)
)
*
-- full workdays in period - workdays in weeks, month, etc.
DateDiffWorkdays(
TimestampToDate(IIF(DateInPeriod([Measures].[Issue created date], [Time].CurrentHierarchyMember),
DateToTimestamp(DateWithoutTime([Measures].[Issue created date])),
DateToTimestamp([Time].CurrentHierarchyMember.StartDate))),
TimestampToDate(IIF(DateInPeriod([Measures].[Issue due date], [Time].CurrentHierarchyMember),
DateToTimestamp(DateAddDays(DateWithoutTime([Measures].[Issue due date]),1)),
DateToTimestamp([Time].CurrentHierarchyMember.NextStartDate)))
)
))
END
The formula might work slow in accounts with large issue count.
Calculated JavaScript custom fields
You can consider calculating the same during import with calculated JavaScript custom fields.
In the code shared below, I am using common JavaScript function workdays from our documentation. You would like to use it to eazyBI advanced settings as well.
So, once you have the common function workdays added to eazyBI advanced settings, you can test and use this custom field definition.
[jira.customfield_plannedestimates]
name = "Planned estimates"
data_type = "decimal"
measure = true
scale = 4
multiple_dates = true
javascript_code = '''
function dateWithoutTime (date) {
var d1 = new Date(Date.parse(date));
d1.setHours(0);
d1.setMinutes(0);
d1.setSeconds(0);
d1.setMilliseconds(0);
return d1;
}
startDate = dateWithoutTime(issue.fields.created);
endDate = dateWithoutTime(issue.fields.duedate);
endDate.setDate( endDate.getDate() + 1 ); // add one day for endDate to count it fully in
totalEstimate = issue.fields.timeoriginalestimate / 3600.0;
nonworkdays = [0,6]; // weekends - Saturday and Sunday
// calculate how many weekdays are in period
weekDaysInPeriod = workdays ( startDate , endDate);
if ( weekDaysInPeriod > 0 && totalEstimate) {
plannedEstimates = [];
workLoad = Math.round((totalEstimate / weekDaysInPeriod) * 10000) / 10000 ; // round to scale
// go in cycle through all days in period from start date till end date and add workload for each workday
dayInPeriod = startDate;
while (dayInPeriod < endDate) {
weekOfDay = dayInPeriod.getDay();
// on weekdays add data entry for each day
if (nonworkdays.indexOf(weekOfDay) == -1 ) {
// add data entry for each day - date and day capacity
plannedEstimates.push(strftime("%Y-%m-%d", dayInPeriod) + "," + workLoad);
}
// get next day
dayInPeriod.setDate( dayInPeriod.getDate() + 1 );
}
issue.fields.customfield_plannedestimates = plannedEstimates.join("\n");
}
'''
Here is a report definition with both approaches. I added measure representing Original estimates hours with due date to see total amount of planned works (not split between dates).
We suggest importing this newly defined custom field as a measure only.
The measure imported with calculated JavaScript might have some rounding problems, though.
Daina / support@eazybi.com
