Report Issues planned vs. actual

Souki_Akhdim · December 19, 2022, 3:50am

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,

Souki_Akhdim · December 21, 2022, 2:47am

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.

VasileS · December 21, 2022, 9:36am

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.

Souki_Akhdim · December 21, 2022, 1:59pm

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

VasileS · December 22, 2022, 8:34am

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.

Kind regards,
V.

Souki_Akhdim · December 22, 2022, 7:54pm

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

oskars.laganovskis · December 30, 2022, 11:59am

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

Souki_Akhdim · January 13, 2023, 3:34pm

Hi @oskars.laganovskis

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

Souki,

Souki_Akhdim · January 23, 2023, 4:56pm

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,

Souki_Akhdim · January 23, 2023, 9:42pm

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”

Souki_Akhdim · January 24, 2023, 7:51pm

@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.

oskars.laganovskis · January 25, 2023, 1:27pm

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.

Souki_Akhdim · February 2, 2023, 9:00pm

Hi @oskars.laganovskis
For some reason, I keep getting “Early” for Features that are “Late”
The only update that I made is use the correct property name for “Planned version” and replace “Start date” with “Release date” since it’s the value that I use.

oskars.laganovskis · February 3, 2023, 9:03am

Hi @Souki_Akhdim,

You might split the expression to find the release dates or names of Planned version and Fix version, and then filter out the suspicious issues for debuging.

The part that returns the date of the Fix version is this.

--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('Release date')),
--order direction
     BASC),
--taking last one item in Tail and retrieving its property
     1).Item(0).Get('Release date')

The part that returns the date of the planned version is this.

--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('Release date')

You might replace the .Get('Release date') with .Name to see the imported name of the versions.

That might help to see the reason why issues are allocated to a specific category.