Rolling average plot on custom field

[chawla.aakash92]

Hi Experts,

I am trying to make a report from eazyBI. My data consists of Projects which have stage 1 date, and stage 2 date (both are custom fields in JIRA)
I want to plot 2 graphs :

a. where x-axis would show ‘Stage-1’ date and y-axis would show ‘Difference of stage-1 and stage -2 dates’ - Scatter Plot
where x-axis would show ‘Stage-1’ date and y-axis would show Rolling average of 4 weeks (of difference between stage-1 and stage 2 dates) Line Graph

I am attaching the snapshot of dummy data and expected graphical representation
for your reference.

[chawla.aakash92]

Expected Graph-1

[chawla.aakash92]

Expected Graph-2

[roberts.cacus]

Hi @chawla.aakash92,

Please provide more details about the data on which you want to build the reports. For example, are the Projects actual Projects in Jira, or are these Jira issues? How are the stage 1 and stage 2 fields defined? How do you intend to retrieve the dates from Jira custom fields if those are actual Jira Projects - do particular issues have the field? Do you use the Projectrak app to add fields to Projects?

Nevertheless, you can use the DateDiffDays() function to calculate the difference between two days - DateDiffDays. For the rest of the requirements, please provide the requested details.

Best,
Roberts // support@eazybi.com

[chawla.aakash92]

Hello @roberts.cacus ,

Thank you for your response.

These P1,P2…are Jira issues.

Stage1 and stage 2 fields are dates.

These dates are custom field defined in Jira. Hence I can get these fields in EazyBI.

I am not sure about the Projecttrak app, as these fields are defined by the Jira admin.

Let me know if you need any other information.

Thank you,
Aakash

[roberts.cacus]

Hi @chawla.aakash92 ,

Thank you for the details. Projects being Jira issues makes the whole thing easier. You can ignore the comment regarding Projectrak, as I assumed your projects were Jira Projects.

Select the Issue dimension in the report rows for the first report. If required to filter by a specific Jira project, use the Issue dimension also in report pages. Next, add the issue property returning the stage 1 date. You can find it under Measures with the name “Issue CUSTOM FIELD NAME”. It should also be imported as a measure with the name “Issues with CUSTOM FIELD NAME”. Add the measure to the report. You can filter the report rows by this measure to return only relevant issues and then remove it. See more details about custom field import in eazyBI here - Jira custom fields.

See some of the suggestions in the picture below. I use the field “Target start”. Thus the property is called “Issue target start” and the measure “Issues with target start”.

Next, define a new calculated measure that will calculate the difference between the two dates for each issue:

CASE WHEN [Measures].[Issues with Target start] > 0
THEN
DateDiffWorkdays(
  [Measures].[Issue Target start],
  [Measures].[Issue Target end]
)
END

The “Target end” represents stage 2 in your case. Next, update the formula to match your use case. Finally, select the newly defined calculated measure in the report. Next, switch the report to the Scatter chart.

I recommend using the Time dimension “Weekly” hierarchy “Week” members in the report rows for the second report. Again filter the report rows by the measure, in my case, “Issues with target start”. Next, define a new calculated measure to calculate the average. See the formula below:

  Avg(
    Filter(
      Descendants([Issue].CurrentMember,[Issue].[Issue]),
      DateInPeriod(
        [Issue].CurrentMember.Get('Target start'),
        [Time].CurrentHierarchyMember
      )
      AND
      [Measures].[Issues with Target start]>0
    ),
    CASE WHEN [Measures].[Issues with Target start] > 0
    THEN
    DateDiffWorkdays(
      [Measures].[Issue Target start],
      [Measures].[Issue Target end]
    )
    END
  )

Again, update the field property and measure names. Finally, define a new calculated measure to calculate the rolling average. See the formula below:

CASE WHEN
  -- show data when at least 4 periods have data
  Rank(
    [Time].CurrentHierarchyMember,
    VisibleRowsSet()
  ) > 3
THEN
  AVG(
    Tail(
      Filter(
        -- filter last 4 periods in current hierarchy
        [Time].CurrentHierarchyMember.FirstSibling:
        [Time].CurrentHierarchyMember,
        [Measures].[Issues with Target start] > 0
      ),
      -- set the count of last periods for running velocity
      4 ),
    [Measures].[AVG duration in period]
  )
END

The report could look similar to the one below in a table:

and line chart views

See the eazyBI documentation page for more information on defining calculated measures -​Calculated measures and members.

Best,
Roberts // support@eazybi.com