Sum doesn't work with Dimesion Status in the Row

[DanielS]

Hi everyone,
I’m a newbie in MDX so i’m trying to figure it out how can I create the following measure:

I have a custom date in jira (Called “Mesa Multidisciplinaria”) to mark an special meeting of the projects. I need to check how many of this meeting were done in the last 7, 15, 30 and 60 days.

I started with the last 7 days (with the first formula, the rest should work… right? )
If I put Issues in Rows with totals I can see the number of issues that meet the filters. However, when I change the row to Status I can’t see the totals:

What am I doing wrong?

To do the formula for the meetings, I started with the basics:

[Measures].[Hoy] = Now()

[Measures].[FechaMesa] = [Measures].[Issue Mesa Multidisciplinaria]

(I created these measures with Integer Format because if I don’t, the following formulas returned “mmm dd yyyy” instead of numbers)

then, I check if the substraction is working:

[Measures].[RestaFechas] =
DateDiffDays([Measures].[FechaMesa],[Measures].[Hoy])

Finally, I tried to do the sum with the filter, starting with those meeting done in the past 7 days:

[Measures].[CountDias] =
Sum(
    Filter(
      [Measures].[Cartera],
      [Measures].[RestaFechas]<8
      AND [Measures].[RestaFechas]>0
     )
     ,[Measures].[Cartera]
  )

Where [Measures].[Cartera] contains all the statutes and issue types that I need to check:

[Measures].[Cartera] =
Aggregate(
[Tipo de Proyecto.].[Programa],
[Issue Type].[Epic])
+
Aggregate(
[Tipo de Proyecto.].[Programa],
[Issue Type].[Sub-Epica])
+
Aggregate(
[Tipo de Proyecto.].[Proyecto],
[Issue Type].[Epic])
+
Aggregate(
[Tipo de Proyecto.].[Proyecto],
[Issue Type].[Sub-Epica])

Thanks a lot!

[martins.vanags]

Hi @DanielS

Your calculation is using the property “Issue Mesa Multidisciplinaria” which returns results only on issue level from “Issue” dimension.
Therefore, it won’t work anymore if you remove the “Issue” dimension from your report and add “Status” dimension.
You would need a function that iterates through issues for each status member.

But in your case, the better way would be using Javascript in advanced settings to define a new interval dimension (use custom field ID instead of NNNNN)

[jira.customfield_memuag]
name = "Mesa Multidisciplinaria age"
data_type = "integer"
dimension = true
javascript_code = '''
dd = issue.fields.customfield_NNNNN
if (dd) {
  issue.fields.customfield_memuag = Date.parse(dd) / 1000 ;
}
'''
time_unit = "seconds"
time_interval = "age"
intervals = "7,15,30,60"
interval_unit = "days"

Then you could customize the intervals for the dimension and group issues by the “Mesa Multidisciplinaria” dates

Martins / eazyBI

[DanielS]

Thanks @martins.vanags !

idk why I don’t have access to Javascript, I guess the administrators in my company could give me access but i don’t know who they are
However, taking your advise of the issue dimension level, I get rid of [Measures].[Cartera] and [Measures].[CountDias] and simplified the formula so this doesn’t happen:

[Measures].[Mesa próximos 7 días] =
NonZero(Sum(
  Filter(
    Descendants([Issue].CurrentMember, [Issue].[Issue]),
    -- interval
    [Measures].[RestaMesa]> -8 and
    [Measures].[RestaMesa]<= 0 
   ),
      -- count relevant issues
      [Measures].[Issues created]
  )
)

Solved!