Pages Filter on two customfields with years as select options

Questions & Answers
, , , ,
1

We are using a bar chart for showing projects date range to provide a “gantt chart”. The default gantt chart is intentionally not used here.

We now need a filter that shows the years in which projects are active as a select list. A project is considered active for a year if the start and end dates are in that year, or if the year is within the start and end dates.

For example, there is project A with a start date of 01.03.2022 and an end date of 01.08.2024. Then, in 2022, 2023, and 2024 the project is considered active and will be displayed upon selecting those years.

Is it possible to use this filter as Pages in the bar chart based on start and end date? Ideally, the years should automatically appear in the dropdown. Below you’ll find a screenshot of the bar chart and the ideal filter view. This is just edited and not a real filter.

Could someone support me with adding the

image
image1813×469 39.4 KB

Best regards

2

Hi, Noah,

Welcom to the eazyBI community.

Could you please tell me what is the project start end end dates? Are these customfields in Jira or calculated from the issues?

Kindly,
Ilze

1 Like
3

Hey @ilze.mezite ,

these are two date customfields. There are not getting calculated from the issue.

Best regards
Noah

4

Hi, @Noah

Thank you for the answer. You can use the Open Issues measure as a starter. Please change the “Created at” and “Resolved at” with your dates.

Then the formula should look something like this:

CASE WHEN [Issue].CurrentMember.Level.Name <> 'Issue' THEN
  Cache(
    NonZero(Sum(PreviousPeriods([Time].CurrentHierarchyMember),
      [Measures].[Issues created]
      - [Measures].[Issues resolved]
    ))
    + [Measures].[Issues created]
    - [Measures].[Issues resolved]
  )
ELSE
  -- optimized formula for drill through Issue
  NonZero(IIF(
      DateBeforePeriodEnd(
        [Issue].CurrentMember.get('Custom Created at'),
        [Time].CurrentHierarchyMember) AND
      NOT DateBeforePeriodEnd(
        [Issue].CurrentMember.get('Custom Resolved at'),
        [Time].CurrentHierarchyMember),
    ([Time].CurrentHierarchy.DefaultMember,
      [Measures].[Issues created]),
    0
  ))
END

Kindly,
Ilze