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

5

Hey @ilze.mezite,

I tried to make it work and receive at least a value with an error message after updating the measure.

CASE WHEN [Issue].CurrentHierarchyMember.Name <> 'Issue' THEN
Cache(
    NonZero(Sum(PreviousPeriods([Time].CurrentHierarchyMember),
      [Measures].[Issue Start date]
      - [Measures].[Issue End date]
    ))
    + [Measures].[Issue Start date]
    - [Measures].[Issue End date]
  )
 ELSE
  -- optimized formula for drill through Issue
  NonZero(IIF(
      DateBeforePeriodEnd(
        [Issue].CurrentMember.get('Start date'),
        [Time].CurrentHierarchyMember) AND
      NOT DateBeforePeriodEnd(
        [Issue].CurrentMember.get('End date'),
        [Time].CurrentHierarchyMember),
    ([Time].CurrentHierarchy.DefaultMember,
      [Measures].[Issue Start date]),
    0
  ))
END

image

Maybe you can give me a hint on how to fix this. However I am not sure if this will lead to my wished result as I need to have the result as filter options in the pages section.

Best regards
Noah

6

Hi, @Noah

Sorry for not answering sooner!

The first part of the CASE isn’t written correctly; for the measures, you need to change the numerical, not date, measures. Please select the suitable measures “Issues with Start Date” and “Issues with End Date”.

The rows starting from 4 with be:

      [Measures].[Issues with Start date]
      - [Measures].[Issues with End date]
    ))
    + [Measures].[Issues with Start date]
    - [Measures].[Issues with End date]
)

Kindly,
Ilze