Show requests created more than * days ago

Questions & Answers
1

Good day, can I find out where I made a mistake, all my cells are empty. I want to create a formula that will show tickets that were created 10 days ago or more, that is, everything that is older than 10 days, thank you.

Sum(
  Filter(
    DescendantsSet([Issue].CurrentHierarchyMember, [Issue].[Issue]),
    DateDiffDays([Issue].CurrentMember.Get('Created at'),
                 [Time].CurrentHierarchyMember.StartDate) > 10
  )
)
2

Hello @JiraDeveloperMD,

Thanks for posting your question!

You were on the right track, however, the Sum() function expects a set and a numerical expression to sum for this set. In this scenario, you can add the CASE statement and use the “Issues created” measure as the numeric expression (this will also allow you to use Page filters to filter the result).

Try using the following formula for your calculated measure:

Sum(
  Filter(
    DescendantsSet([Issue].CurrentHierarchyMember, [Issue].[Issue]),
    DateDiffDays(
      [Issue].CurrentMember.Get('Created at'),
      [Time].CurrentHierarchyMember.StartDate
    ) > 10
  ),
  CASE WHEN
  (
    [Measures].[Issues created],
    [Time].CurrentHierarchy.DefaultMember
  ) > 0
  THEN
  1
  END
)

Don’t hesitate to contact us at support@eazybi.com if you have any further questions.

Best,
Marita // support@eazybi.com

3

Thank you, there is another problem: when I select in PAGES → Time → Q1 2024 everything displays fine, but when I choose to display all tickets for all time (All Times), it shows nothing (though JQL in Jira displays everything correctly) and also takes a long time to load. What could be the problem and how can it be solved?

4

Hello @JiraDeveloperMD,

This is happening because the formula I shared is designed to interact with specific members of the Time dimension. When you select “All times” (effectively removing specific time members from consideration), eazyBI is left without a reference point against which to compare dates.

If you would like to use All times in your report, I suggest using the following formula instead:

Sum(
  PreviousPeriods(
    IIf(
      [Time].CurrentHierarchyMember.Level.Name = "(All)",
      [Time].[Day].DateMember(DateAddDays(Now(),-10)),
      [Time].[Day].DateMember(DateAddDays([Time].CurrentHierarchyMember.StartDate,-10))
    )
  ),
  [Measures].[Issues created]
)

This expression calculates the total number of issues created in the time periods leading up to 10 days before either today’s date (if at the aggregated “All” level of the Time dimension) or 10 days before the start date of the current Time dimension member (if at a more detailed level).

You can read more about IIf statement in our documentation here: IIf statement
And PreviousPeriods here: PreviousPeriods

Hope this helps!

Best,

Marita // support@eazybi.com

5

I wrote it like this and it seems to work fine too

Sum(
  Filter(
    Descendants([Issue].CurrentMember, [Issue].[Issue]),
    DateDiffDays(
      [Issue].CurrentMember.get('Created at'),
      Now()
    ) > 5
  )
)
6

Hi @JiraDeveloperMD,

Thanks for sharing your formula!

It will certainly work if you just want to see all Issues that are created more than 10 (or 5) days before today or “now”.

However, it will now work dynamically with the Time filter. If you choose a different Time period, it would still return results against “now” as a reference instead of the selected Time period.

Best,
Marita // support@eazybi.com