Two field pie chart

Questions & Answers
1

I have two fields in Jira. A target date and a delivery date. These fields come in as measures. I want to create a pie chart in Eazy Bi, which has to categories the first is on time or early, this is if the delivery date is on or before the target date. The second is late, this if the delivery date is after the target date. I also would like a “filter” on this that looks at the delivery date and only shows me the information for the delivery date performance over the last 80 days. Any help would be much appreciated as I am stuck!

2

Hi @CKirkland ,

There are two approaches you can take to make the pie chart.

Option 1: Create two calculated measures

In the Measures, create two separate calculated measures – one for counting early delivered issues and another for counting late issues. The calculation principle is to iterate over all issues and compare both dates for each. For the calculations, use the measure “Issues with delivery date” as the core element so you can filter the report using the “Time” dimension and get issues.

The expression for Early delivered issues issue count might look like below:

Sum(
  --set of issues with target date and a delivery date
  Filter(
    Descendants([Issue].CurrentHierarchyMember, [Issue].[Issue]),
    NOT IsEmpty([Measures].[Issue target date])
    AND --delivery date falls in selected period
    DateInPeriod(
      [Measures].[Issue delivery date],
      [Time].CurrentHierarchyMember
    )
  ),
  --count early issues where target >= delivery
  CASE WHEN
    DateCompare(
      [Measures].[Issue target date],
      [Measures].[Issue delivery date]
    ) >= 0
  THEN --count issue and show on timeline by delivery date
    [Measures].[Issues with delivery date]
  END
)

Set measure formatting to Integer.

For the Late delivered issues, subtract the early delivery issue count (calculated measure you just created) from all Issues with a delivery date:

[Measures].[Issues with delivery date] 
- [Measures].[Early delivered issues]

Select both calculated measures in the report columns. Set the Time dimension on rows or pages to get a report only for the issues with the Delivery date in the selected period.

More details on calculated measures are described in the documentation: Calculated measures.

Option 2: Create a JavaScript Calculated Field

If you have a large number of issues (the calculated measures are timing out) or need to use this categorization across multiple reports, consider creating a JavaScript calculated field (Account specific calculated fields ).

Create a new dimension “Delivered on time” to group all issues: early, late, nor delivered yet. See this community post on how to make such dimension: https://community.eazybi.com/t/javascript-calculated-custom-field-dealing-with-two-dates/18975/2.

In the report, choose the measure “Issues with delivery date” on the columns and the new dimension on rows. Set the Time dimension on pages to generate a report for only the issues with the Delivery date in the selected period.

Best,
Zane / support@eazyBI.com

3

That is fantastic, thank you very much. I am now trying to insert a time offset of 2 days i.e. if something is two days late it still counts as on time. I have tried altering the 0 below thinking that may enable that but it doesnt seem to work, have you got any thoughts on this?

4

The function DateCompare() returns three values -1, 0, and 1. It only compares two dates but does not look for difference between them.

If you have a margin of days that may be overlooked, then use the function DateAddDays() to adjust the target date. For example, move the target date 2 days further and then compare it with the actual delivery date.

DateCompare(
  --increase target date by 2 days to give more time
  DateAddDays([Measures].[Issue target date],2),
  [Measures].[Issue delivery date]
) >= 0

Or you can use the DateDiffDays() function to calculate the actual number of days between the target and delivery dates.

DateDiffDays(
  [Measures].[Issue target date], 
  [Measures].[Issue delivery date]  
) >= -2 -- compare with allowed margin
5

Absolutely fantastic, thank you very much.

1 Like