Sum two measures

I need to sum two calculated formulas, I’m trying in a logical way but it is causing me timeout error.
Here they are my calculated formulas:

Contract execution date sum

NonZero(
   Sum(
 Filter(Descendants([Issue].CurrentMember, [Issue].[Issue]),
  IsEmpty([Measures].[Issue Contract Execution Date])
),
-- counter by measure works as a filter as well:
([Measures].[Issues created],
     [Time].CurrentHierarchy.DefaultMember)
  )
)

Contract execution date total
It’s the sum of the Issues with the contract execution date and the previous formula

  [Measures].[Issues with contract execution date] +
  [Measures].[Contract execution date sum]

I know it’s not the best practice to do it on this way, can you please advise how to do it on the correct way? Thanks!

Hi,

The reason why the formula hits the timeout is that the construction of the functions of Filter and Descendants iterates over the full set of issues. That can often reach the timeout depending on the server hardware performance and the number of issues in your account.

The workaround for this problem is to implement a Javascript calculated custom field in eazyBI and create a custom dimension allowing to efficiently filter issues having the empty value in the custom field of Contract Execution Date.

For that, you need to add the following lines to your advanced settings:

[jira.customfield_has_execution_date]
name="Has execution date"
data_type="string"
dimension=true
javascript_code='''
if (issue.fields.customfield_NNNNN) {
    	issue.fields.customfield_has_execution_date="Yes"
 } else {
    	issue.fields.customfield_has_execution_date="No"
}
 '''

Note to replace the NNNNN with the correct ID of the Contract Execution Date field.
Once you save the settings, you should see the new custom field available for import. Please, select the field for import as a new dimension. After the data import, you will have the dimension, and the formula for the Contract execution date sum measure now is the following:

([Measures].[Issues created],
 [Has execution date].[No],
[Time].CurrentHierarchy.DefaultMember)

Kindly,

Janis, eazyBI support

1 Like