Need Help Optimizing a Formula

Questions & Answers
, ,
1

Hello everyone Eazybi Community,

I’m currently using the following formula, but I’m experiencing issues with the report charts not rendering due to memory errors and query timeouts.

I’ve spent the last two days trying to optimize the formula, but haven’t been able to make any improvements. I’m posting to the community in hopes of getting some assistance with refining it.

ERROR:

Failed to execute query. Error message:
OutOfMemory used=1945907808, max=2147483648

CODE Formula:

NonZero(Avg(
  Filter(
    Descendants([Issue].CurrentMember,[Issue].[Issue]),
    ---Filter by time whem the value for any issuew
    DateInPeriod(
      [Measures].[Issues created],
      [Time].CurrentHierarchyMember
    )
  ),
  CASE WHEN
  (
    [Measures].[Issues created],
    [Time].CurrentHierarchy.DefaultMember)
    >0
    AND
    [Measures].[Issue status] = "Done"
    THEN
    DateDiffWorkdays(
      [Measures].[Issues with 접수일],
      [Measures].[Issue resolution date],
      '67'
    )
    END
))

Report:

  • Pages: Time
  • Rows: Project
  • Columns: Measures

Thank you for your help.

2

Hi @NextLife

Your formula is on the right track; however, it needs some adjustments.

The main issue is that you’re referencing numerical measures in formulas that expect date values.
Replace the
[Measures].[Issues created] with [Measures].[Issue created date]
and
[Measures].[Issues with 접수일] with [Measures].[Issue 접수일]

Here’s an optimized formula:

NonZero(
  Avg(
    Filter(
      Descendants([Issue].CurrentMember, [Issue].[Issue]),
      -- Filter by time whem the value for any issue
      DateInPeriod(
        -- use the Issue created date property here
        [Measures].[Issue created date],
        [Time].CurrentHierarchyMember
      )
      AND
      [Measures].[Issue status] = "Done"
    ),
    CASE WHEN
      [Measures].[Issues created] > 0
    THEN
      DateDiffWorkdays(
        -- use the Issue 접수일 property here
        [Measures].[Issue 접수일],
        [Measures].[Issue resolution date],
        '67'
      )
    END
  )
)

​Best regards,
Nauris

1 Like