Report Issues planned vs. actual

Hi There,

I’m building a report tat shows how many issues were closed on time vs. how many were delayed.
For that I will be using the Fixversion field in Jira and a custom filed Planned Fixversion.
For now I’m using “Matches” function. However, this is not going to render accurate results.
I will need to compare between the version release dates to tell if an issue was closed on time.

If planned version date greater than Fixversion date → Late
If planned version date is = or < then Fixversion date → on Time
I think empty planned fix version should be on Time as well and maybe color the cell as a warning.

Any idea how I can approach this or any examples that can help would be very appreciated.

Thanks,

I got this so far, I was able to display the fix version and the planned but no luck with the date compare -
I tried
CASE WHEN --compare two dates
DateCompare(
[Fix Version].CurrentHierarchyMember.get(‘Release date’),
[Planned Fix Version].CurrentHierarchyMember.get(‘Release date’)
) > 0
THEN --if Fixversion date > Planned Fix date
“OK”
END
I did not get any results.

Any tips would be appreciated.

Hello @Souki_Akhdim

Here’s an example that might help (or at least will give you a starting point): Issues due and overdue - Issues - Jira Demo - eazyBI

Reports in here (Analyze - Jira Demo - eazyBI) can be exported then imported in your account and all calculated measures will be available for you.

Hope it helps!
Vasile S.

Hi @VasileS

I did review this example, it uses due date and Time.
My issue is that I don’t use due date for the tickets but only Fix version/ Planned version which are linked to a release date in Jira. SO I need to be able to compare those two dates to have accurate information.
something like this

Fixversion release date Planned Version rleased date On time Late
Issue 1 12/21/2022 11/21/2022 1
Issue 2 1/15/2022 12/13/2021 1
Issue 3 5/10/2022 empty 1

Hello @Souki_Akhdim

OK… this looks more tricky than I thought…
As you mention “[Fix Version].CurrentHierarchyMember” and “[Planned Fix Version].CurrentHierarchyMember” I assume these are dimensions in your cube. Are they already in ROWS sections of your report? So that “CurrentHierarchy” will make sense?
Yet… I am not sure how is this working with 2 Fixversions (fix and planned).

What I would try is to create a couple of new “report specific calculated measure” as
[Fix Version].CurrentHierarchyMember.get(‘Release date’)
and
[Planned Fix Version].CurrentHierarchyMember.get(‘Release date’)
so I can see what is displayed.

Still… I must decline my competence from this moment… You may want to contact directly EB support and send them the full report definition.
image

Kind regards,
V.

1 Like

Thanks @VasileS for taking the time to look into this.
I will give a short to the report specific measures and see what I get, If not I will contact support :slight_smile:

Hi @Souki_Akhdim,

Although the fields in Jira use the same set of versions within the project, the custom field data coming to eazyBI only retains the version name.
Therefore, retrieving version properties the same way as for the user dimensions is impossible.

The expression needs to filter the versions of the relevant project to retrieve the one matching the planned version to retrieve the required properties.

It is also possible to assign multiple fix versions for the issue. Therefore, you might need a filter for the first or the last version. Sometimes, the versions might be assigned in a mixed order and the last one in the list might not be the one with the latest date.
I created an expression that checks if the issue has both Fix version and Planned version populated and then finds the Start date of the last fix version and compares that to the Start date of the Planned version.

The expression returns the following results.
“no planned version” - when the planned version is not selected
“early” - when the planned version date is less than the latest assigned fix version date
“match” - when versions have the same start date
“late” - when the planned version start date is later than the latest fix version date

Please see the expression below.

CASE
 WHEN
  IsEmpty([Issue].CurrentHierarchyMember.Get('Planned version'))
    OR
  [Issue].CurrentHierarchyMember.Get('Planned version') = "(none)"
 THEN
--no planned version set
"no planned version"
 WHEN
   NOT IsEmpty([Issue].CurrentHierarchyMember.Get('Fix version IDs'))
  AND
   NOT 
    IsEmpty([Issue].CurrentHierarchyMember.Get('Planned version'))
  AND 
   NOT
    ([Issue].CurrentHierarchyMember.Get('Planned version') = "(none)")
 THEN
CASE WHEN
 CACHE( 
  DateCompare(
--last assigned fix version start date
    Tail(
     Order(
--set of related fix versions
      [Fix Version].[Version].GetMembersByKeys(
        [Issue].CurrentHierarchyMember.Get('Fix version IDs')),
--value for order - fix version start date
     DateToTimestamp([Fix Version].CurrentHierarchyMember.Get('Start date')),
--order direction
     BASC),
--taking last one item in Tail and retrieving its property
     1).Item(0).Get('Start date'),
--committed planned version start date
  Filter(
    DescendantsSet(
      [Fix Version].[Project].GetMemberByKey(
       [Issue].CurrentHierarchyMember.Get('Project key')),
      [Fix Version].[Version]),
      [Fix Version].CurrentHierarchyMember.Name = [Issue].CurrentHierarchyMember.Get('Planned version')
      ).Item(0).Get('Start date')
  )) >0 
  THEN "early"
  WHEN 
DateCompare(
--last assigned fix version start date
    Tail(
     Order(
--set of related fix versions
      [Fix Version].[Version].GetMembersByKeys(
        [Issue].CurrentHierarchyMember.Get('Fix version IDs')),
--value for ordering - fix version start date
     DateToTimestamp([Fix Version].CurrentHierarchyMember.Get('Start date')),
--order direction
     BASC),
--taking last one item in Tail and retrieving its property
     1).Item(0).Get('Start date'),
--planned version start date
  Filter(
    DescendantsSet(
      [Fix Version].[Project].GetMemberByKey(
       [Issue].CurrentHierarchyMember.Get('Project key')),
      [Fix Version].[Version]),
      [Fix Version].CurrentHierarchyMember.Name = [Issue].CurrentHierarchyMember.Get('Planned version')
      ).Item(0).Get('Start date')
  ) = 0  
  THEN "match"
  WHEN 
 DateCompare(
--last assigned fix version start date
    Tail(
     Order(
--set of related fix versions
      [Fix Version].[Version].GetMembersByKeys(
        [Issue].CurrentHierarchyMember.Get('Fix version IDs')),
--value for order - fix version start date
     DateToTimestamp([Fix Version].CurrentHierarchyMember.Get('Start date')),
--order direction
     BASC),
--taking last one item in Tail and retrieving its property
     1).Item(0).Get('Start date'),
--planned version start date
  Filter(
    DescendantsSet(
      [Fix Version].[Project].GetMemberByKey(
       [Issue].CurrentHierarchyMember.Get('Project key')),
      [Fix Version].[Version]),
      [Fix Version].CurrentHierarchyMember.Name = [Issue].CurrentHierarchyMember.Get('Planned version')
      ).Item(0).Get('Start date')
  ) <0
  THEN "late"
 END
END

You might then use conditional formatting for this measure to color the cells according to their value.
Please read more about conditional formatting here - Conditional cell formatting.

Regards,
Oskars / support@eazyBI.com

Hi @oskars.laganovskis

Thanks you for this, I will review it more closely and get back to you.

Souki,

Hi @oskars.laganovskis

For some reason the expression only returns “no planned version” even if the Planned and Fix versions match, can you please advise?

Thanks,

image

Since I’m not using multiple fix version. I tried to simplify the code to compare between Release date of fixversion and release date of Planned fix version but I still get a Blank. Not sure what I’m missing.

CASE WHEN
CACHE(
DateCompare(

Filter(
DescendantsSet(
[Fix Version].[Project].GetMemberByKey(
[Issue].CurrentHierarchyMember.Get(‘Project key’)),
[Fix Version].[Version]),
[Fix Version].CurrentHierarchyMember.Name = [Issue].CurrentHierarchyMember.Get(‘Fix version IDs’)
).Item(0).Get(‘Release date’),

Filter(
DescendantsSet(
[Planned Fix Version].[Project].GetMemberByKey(
[Issue].CurrentHierarchyMember.Get(‘Project key’)),
[Planned Fix Version].[Version]),
[Planned Fix Version].CurrentHierarchyMember.Name = [Issue].CurrentHierarchyMember.Get(‘Planned Fix Version’)
).Item(0).Get(‘Release date’)
)) <0
THEN “Late”

@oskars.laganovskis the script is working perfectly - Thank you.
I did split it code so I can have separate columns with the count. I have a couple discrepancies in the count, I’m looking into that.

image

Hi @Souki_Akhdim,

You probably had imported the “Planned version” under a different property name. Therefore this part returned true and lead to “no planned version”.

IsEmpty([Issue].CurrentHierarchyMember.Get('Planned version'))

It seems that you have already fixed that.