How itereate on Descendants but just only first level, without nested linked issues

Dario_Tjor · August 22, 2024, 2:24pm

Hi Community

I am trying to craft a new report to visualize withn a Time Dimension: All tickets that have reopens in order to fetch lastly some metrics about, for example: avg or repons, sum, etc.

You can see on my first approach that I could get firtly (1st column) all distincts ticket with reopens but, when trying to calculate the amount of reopens by period and as sum for parent levels I am not able to find the correct result.

See below the first approach and calculation.

--annotations.total=sum
CASE WHEN
       [Issue].CurrentHierarchyMember.Level.Name = "Project"  OR
       [Issue].CurrentHierarchyMember.Level.Name = "All Issues"   AND
       DateInPeriod( 
        [Measures].[Issue resolution date],
        [Time].CurrentHierarchyMember)
     THEN
       NonZero(sum(
            Filter(Descendants([Issue].CurrentMember,[Issue].[Issue]),
              [Issue].CurrentHierarchyMember.get('Reopened')>0
              AND
               DateInPeriod( 
                    [Measures].[Issue resolution date],
                    [Time].CurrentHierarchyMember)
            ),             
             [Measures].[Reopened resolved]                 
          ))
       
     ELSE
       [Issue].CurrentHierarchyMember.get('Reopened')
END

I guess, as I am very new on these, I am mixing up concepts. I try settostr function and found that nested tickets are considering till the last leaves linked issues and that could be the reason why so, please I need help from some more experimented user here to guide me or gave some clue.

I.e., when applying to settostr to the filter I found that when the issue has one or more linked issues they are also tracking under the iteration and that is the reason, I guess, from wich the top level: Project or All Issues summarized 81 but also the same occurs with the individual issues where if reopen for individual issue is 1 and the nested is 1 it sums up 2.

I would like to see on Test columns:

for each individual issue the value of [Issue].CurrentHierarchyMember.get(‘Reopened’) : 0…n reopens and for the top level: default or project the summarization of the individual issues shown below for the time dismension selected.

Many thanks upfront,
Darío

Dario_Tjor · August 22, 2024, 6:13pm

Hi,
I keep on working with it and found some conceptual issues in my previous work. My last try is like follows:

--annotations.total=sum
CASE WHEN 
      (
       [Issue].CurrentHierarchyMember.Level.Name = "Project"   OR
       [Issue].CurrentHierarchyMember.Level.Name = "(All)" 
      )
     THEN  
     -- True
    
         sum( Filter([Issue].CurrentMember,
              [Issue].CurrentHierarchyMember.get('Reopened')>0
               AND
              DateInPeriod( 
                [Measures].[Issue resolution date],
                [Time].CurrentHierarchyMember) 
                ),
            [Measures].[Reopened resolved]
          ) -- end sum
     
       ELSE  
     -- False     
         [Issue].CurrentHierarchyMember.get('Reopened')      
       END

Now the only issue is that the summarized block when level is project or (all) does not show the sum of all inidividuals below.and if I troubleshoot this block it works ok alone; i.e, for example if I replace it with a constant, it shows the constant but if I left the sum(filter)_block it is not calculated…

Better but it is not what I am looking for.

nauris.malitis · August 29, 2024, 1:41pm

Hi @Dario_Tjor

The measure “Reopened resolved” should show you the number of times each individual issue reopened and also sum up the values to higher levels, like “All Issues” and Project members.

Or is this not your goal?

Would you like to show the number of reopens in the higher levels or the count of issues where the reopens happened at least once?

Best regards,
Nauris

Dario_Tjor · August 29, 2024, 3:43pm

Hi Mauris,

Good afternoon and thanks for replying my doubt.

Finally I check the following expression:

– annotations.disable_drill=true
IIf(
[Issue].CurrentHierarchy.Level.Name = ‘Project’ OR
[Issue].CurrentHierarchy.Level.Name = ‘(All)’ ,
Sum(
VisibleRowsSet(),
sum([Measures].[individual issues reopened resolved])
),
[Measures].[Reopened resolved]
)

and it worked, but I really do not like using visible option enough. I believe that for some context it won’t work (calculate goo).

Is there any other option to do it in a better, perfomance way?

Thanks upfront,

Darío

nauris.malitis · August 30, 2024, 6:11am

Hi @Dario_Tjor

Could you please tell me what exactly you would like to show for the individual issues and what value you would like to show for upper levels (All Issues, Projects, etc.)?

I feel like I’m missing the main requirement without understanding what exact values you would like to show for each level.

Best regards,
Nauris

Dario_Tjor · August 30, 2024, 12:09pm

Hi Mauris,

Good morning.

I need to craft a report showing metrics in relationship to reopens as main metric of quality so, I want to show:

all distinct tickets that had at least one reopen within the time dimension period
all accrual reopens in period
% of reopens over total tickets created in period

The report should be flexible enough to show the right numbers not only when looking at inidividual issues but also when looking at default category /all issue memebers at the top of the column, you know. but always just oncisdering the ones that fall into the time period dimension you have selected on pages.

Thanks upfront,

Darío

nauris.malitis · September 4, 2024, 12:43pm

Hi @Dario_Tjor

Thanks for the additional details!

In the third metric you wish to calculate the % of reopens over total tickets created in the period, does this mean that the first two metrics should also look at issues reopened, that are created in this same period?

Best regards,
Nauris

Dario_Tjor · September 4, 2024, 2:05pm

You’re right! The idea is retrieving metrics of reopened within a time dimension so we can reflect some kind of quality assurance perfomance and opportunities to improve in that way.

Many thanks!

Darío.

nauris.malitis · September 4, 2024, 2:52pm

Hi Dario,

But should the time interval apply to the creation date?
For example, show the number of reopened tickets, that were created in Aug 2024.
Or show the number of reopened tickets that were reopened in Aug 2024, no matter when they were created?

Best regards,
Nauris

Dario_Tjor · September 4, 2024, 3:10pm

Hi

IT should be applied to the reopened date in period, as we want to measure the impact of reopens in such a period. I.e., at least so far, we are not worried about aging but we want to focus on quality in period (later we will ).

Thanks!

Darío

nauris.malitis · September 23, 2024, 11:15am

Hi @Dario_Tjor

Thanks for the details, and I apologize for the late reply!

The missing detail here is the date when the reopening happened.
Could you please share more details about the “Reopened” field?
Is it a custom field in your Jira, which is a single-value numeric field that holds a numeric value for the number of times the issue has been reopened?
Or has this field been created in eazyBI with some custom code?

The solution would be simpler if the creation or resolution date of the issue would be at the base of it, as reopened dates can be several for each issue, so how would you show them up, if one issue was reopened in Aug and also in Sep? Would you expect the same issue to show up each time in each month or only count it towards the last reopen date in Sep?

If created or resolution date of an issue would fit your use case, you could could use a formula like this:

Count(
  Filter(
    Descendants([Issue].CurrentMember, [Issue].[Issue]),
      [Issue].CurrentHierarchyMember.get('Reopened')>0
      AND
      [Measures].[Issues created]>0
  )          
)

This would return you the number of reopened issues that were created in your selected Time period. This measure will also work with higher levels of dimensions or non-issue dimensions as well.

Best regards,
Nauris

Dario_Tjor · September 23, 2024, 12:11pm

Hi Nauris,

Good day!

Let me clarify your doubts:

Objective:

Show some kind of quality measure as reopen-metric evolution during a selected dimension of time.

What: (in period)

Issues created: total
issues reopened (just distinct issues no the frequency of reopened times in period) Total. The trigger here should be the reopened date that it should match the range of dimension period between in
Issues reopened accrual: now yes, the total amount of reopens by issue. No matter when if only one of them falls into the time period dimension. Ej issue xxxx has an reopened within [Time Dimension] => match the criteria
Other metrics
- ratio of issue reopens / total reopens (by issue type) Ej issue x has 2 reopens / total reopens in time dimension
- avg of issues reopens by issuetype. No t easy as it should be some cache calculus for issue type previously

I hope I could clarify just a bit the challenge.

Thanks upfront,

Darío

nauris.malitis · September 23, 2024, 1:09pm

Thanks for the details, Dario!

About the “Reopened” field. Is this a single-value numerical field in Jira that you manually fill out?
Are you simply importing it as a measure and as a property in eazyBI or do you have any additional code added to this field?

Understanding the field better would allow me to figure out the best way how to extract the dates of these reopenings.

Thanks!
Nauris

Dario_Tjor · September 23, 2024, 2:09pm

Hi

Reopened is just a counter filed that is automatically increased when this event happens on an issue basic…

I do belive it is a non-custom field and, yes, it is imported on a daily basic wihout any further calculus ad-hoc. It is just retrieved via the import action and it lives on Jira DB as stand alone field.

Thanks!
Darío.

nauris.malitis · September 24, 2024, 8:57am

Hi @Dario_Tjor

Thanks for the details!

If this is a standard numeric field, you should be able to import value changes for this field.

Go to the Source Data tab, click “Edit” on your Jira source and in the Custom fields section find the “Reopened” field and select the checkbox “Import values changes” and run the import.

After the import finishes, you should see measures like “Reopened change” and “Reopened history” in your Measures dimension.

The following formula will return the number of issues that had a change in your Reopened field in your selected Time period:

Count(
  Filter(
    Descendants([Issue].CurrentMember, [Issue].[Issue]),
      [Issue].CurrentHierarchyMember.get('Reopened')>0
      AND
      [Measures].[Reopened change]>0
  )          
)

If you have, for example, months in Rows, this measure will show the count of issues where the reopened changed in each of the month. So, the same issues will show up at different months, if they had changes in each of these months.

Next, the following measure will be based on issue creation date and will, for a Time period, show how many issues were created in this period and have been reopened (also in other periods), divided by the number of issues created in the period.

CASE WHEN
  [Measures].[Issues created]>0
THEN
  Count(
    Filter(
      Descendants([Issue].CurrentMember, [Issue].[Issue]),
        [Issue].CurrentHierarchyMember.get('Reopened')>0
        AND
        [Measures].[Issues created]>0
    )          
  )
  /
  [Measures].[Issues created]
END

The “Reopened change” measure should show you the number of reopens in each Time period. You can click on the column header of this measure and choose “Add calculated” → “Cumulative sum” → “including empty” to see the accrual reopens over several time periods.

To see these metrics divided by issue type, you can use the Issue Type dimension as a Page filter to filter by the type you’re interested in, the measures should reflect the values accordingly.

Let me know if this fits your use case and if the numbers are coming up as expected!

Best regards,
Nauris

Dario_Tjor · September 24, 2024, 3:25pm

Thanks you for the time invest on my questions.

I will be making some tests and look forward the results.

I will mark the thread as solution.

Best regards,

Darío