Calculate Cumulative Sum only for the rows of a Particular Issue

Questions & Answers
, , ,
1

Hi All,

I need to find the cumulative sum of of my column - Avg Workdays in Transition for each issue.

I have a report say as follows :
image

I have the constraints as follows:
1.Each Issue may not have all the transitions. So, the rows vary, as you can see in the picture. e.g. Issue A has 3 transitions , but Issue D can have 2, even Issue E has 1. It can happen.

Q1. How to find the cumulative sum for each issue , like mentioned in the picture.

Q2. How to find the cumulative sum and only show it in the last column for that issue, like the column Cumulative Final Sum

Please help.

Regards,
Soumen

2

Hi,

Try creating a new calculated measure (with integer formatting) using this formula

CASE WHEN
[Measures].[Workdays in transition status]>0
THEN
  Sum(
  Head(
      Order(
      Filter(
      [Transition].[Transition].Members,
      [Measures].[Workdays in transition status]>0
      ),
      [Transition].CurrentMember.key,
      BASC
      ),
    Rank(
    [Transition].CurrentMember,
      Order(
      Filter(
      [Transition].[Transition].Members,
      [Measures].[Workdays in transition status]>0
      ),
      [Transition].CurrentMember.key,
      BASC
      )
  )
  ),
  [Measures].[Workdays in transition status]
  )
END

Then you could create a calculated member in the “Transition” dimension using aggregate to force the correct order for Transition dimension members.

Martins / eazyBI support

image
image1746×1346 122 KB

image
image1830×944 81.7 KB

3

Hi @martins.vanags

Thanks for the suggestion.
I have followed the steps. The calculated member in Transition dimension is working fine.
I have the transitions as follows:
Ready for dev->In progress->ready for qa-> QA → ready for ft → ft

There maybe some backtrack status like ,QA → In Progress. But as of now above is my target.

But the calculated measure is not working in a cumulative way and also they are not matching with Workdays in Transition values. Here’s the screenshot

Attachment
Attachment349×620 50.8 KB

Note: I can have 0 values in any transition. So I tried to use greater than equal in the formula as well. But failed.

Here’s my calculated member in Transition dimension

Aggregate({[Transition].[In Progress => Ready for QA],
[Transition].[Ready for QA => QA],
[Transition].[QA => ready for ft],
[Transition].[ready for ft => FT]})

Please help…

4

Try this code for defined workflow order:

Sum(
    Head(
      Childrenset([Transition].[Jira workflow]),
      Rank(
        [Transition].CurrentMember,
        Childrenset([Transition].[Jira workflow])
      )
    ),
  [Measures].[Workdays in transition status]
  )
5

Hi @martins.vanags

It is working . Thank you for help.

But I am facing a challenge on the issue summary.
I have used Time dimension as Previous month on the Page tab, to filter out my issues.
As a result, I am getting only those issues who have the ‘Jira Workflow’ transitions made on previous month.

But when i am exclusively trying to get the issue names by using [Issue].CurrentMember.name, I am getting all other issues as well in my report. But I want only those issue names that I got after calculating the cumulative sum.

Any help or suggestion for this please…

6

Hi @martins.vanags ,

Any help on the above issue?
My other calculations are dependent on it.

7

Please use “Nonemtpy” cross join for report rows to show issues related to page filter.
https://docs.eazybi.com/eazybi/analyze-and-visualize/create-reports#Createreports-Pagedimensions

If that didn’t help, reach out to support@eazybi.com and provide the report definition and more details about your use case.

Martins / eazyBI team