New to Eazy Bi, need to report based on a drop down selection

Questions & Answers
1

Hi everyone,

I created a report where it displays the average # of days that a custom field “Dropdown A” has the option “Option A” selected and is still open, similar to the built in Measure “Average Age of Open Issues” with this formula

CASE WHEN [Measures].[Open issues] > 0 THEN
  Avg(
    Filter(Descendants([Issue].CurrentMember, [Issue].[Issue]),
      -- filter open issues in period using issue properties Created date and Resolution date only
      DateBeforePeriodEnd(
        [Issue].CurrentMember.get('Created at'),
        [Time].CurrentHierarchyMember) AND
      NOT DateBeforePeriodEnd(
        [Issue].CurrentMember.get('Resolved at'),
        [Time].CurrentHierarchyMember)
      ),
    CASE WHEN
    ([Measures].[Issues created],
    [Time].CurrentHierarchy.DefaultMember) > 0
    THEN
    -- cumulative age of each issue for any period till end of period or till today
      CASE WHEN DateInPeriod(Now(), [Time].CurrentHierarchyMember)
      THEN DateDiffDays([Issue].CurrentMember.get('Created at'),
        Now())
      ELSE DateDiffDays([Issue].CurrentMember.get('Created at'),
        [Time].CurrentHierarchyMember.NextStartDate)
      END
    END
  )
END

This displays the data fine but only for the months that an issue was created with those criterias. How can I display the data to be displayed in the preceeding months where no issues created?

For example, issues with those criterias were created in August and nothing after that but I would still like to see those August issues displayed in Sept. Oct, Nov, Dec becuase those issues are still open

2

Hi @Haig,

To make a similar calculation for custom field changes, you should get the timestamps when the custom field was set to “Option A” (analogy to Created at date) and when “Option A” was cleared or changed (analogy to Resolved at date).

Yuo can import the change history for the “Dropdown A” custom field: Import issue change history.
Then, you can find the first date when the field value is set to Option A. In Measures, define a new calculated measure that would look for the timestamp for a specific transition of Dropdown field A (more on calculated measures here: Calculated measures).

--convert timestamp to date
TimestampToDate(
  --check when the issue got value option A for the first time ever
  DefaultContext((
    [Measures].[Transition to first timestamp],
    [Transition Field].[Dropdown A],
    [Dropdown A].[Option A],
    [Issue].CurrentHierarchyMember
  ))
)

And a similar calcauted measure to find when Option A was cleared.

--convert timestamp to date
TimestampToDate(
  --check when the issue cleared value option A for the last time ever
  DefaultContext((
    [Measures].[Transition from last timestamp],
    [Transition Field].[Dropdown A],
    [Dropdown A].[Option A],
    [Issue].CurrentHierarchyMember
  ))
)

Then, update the expression for the average duration of Option A and refer to the new calculated measure instead of the issue properties ‘Created at’ and ‘Resolved at’'.

The alternative is to find those two dates using JavaScript-calculated custom fields and import them as new issue properties: Account specific calculated fields.

Best,
Zane / support@eazyBI.com

3

Thank you Zane. That worked!!

1 Like