Count open issues in custom data import

Questions & Answers
,
1

Hi,

I am importing ticket data from a ticket tool which is not Jira.

In the import settings I checked the option “Date count measure” for both creation and resolution date field, which works perfect:

image

What I want now, is to know about the open tickets at every date, too.

I first created a measure to count manually the state of each ticket, this measure doesn’t know about the date. :frowning:

Then I built a measure that calculates the difference between the two “Date count measures” I created in the import, which gives the correct value for each day. I then added the “cumulative sum” calculation on the measure:

image

This now gives the correct value but only if I am looking at the whole timeline. As soon as I only look at a certain period within, it’s not correct as it only looks at the opened and resolved tickets of that period ignoring the open tickets from before.

I hope I could explain my problem well and someone has a good idea to help me here!

Best
Sven

2

Hi @sven,

Always happy to see your name :wave: ! The best option could be re-using the predefined calculated measure from the Jira issues cube - “Open issues”. See its formula below for reference:

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]
  )
WHEN [Time].CurrentHierarchyMember IS [Time].CurrentHierarchy.DefaultMember
THEN NonZero([Measures].[Issues due])
ELSE
  -- optimized formula for drill through Issue
  NonZero(IIF(
      DateBeforePeriodEnd(
        [Issue].CurrentMember.get('Created at'),
        [Time].CurrentHierarchyMember) AND
      NOT DateBeforePeriodEnd(
        [Issue].CurrentMember.get('Resolved at'),
        [Time].CurrentHierarchyMember),
    ([Time].CurrentHierarchy.DefaultMember,
      [Measures].[Issues created]),
    0
  ))
END

Replace the Issue dimension reference with your Ticket dimension and level. Update the property references in the ELSE branch. Replace measures “Issues created” and “Issues resolved” with your “Tickets Created Count” and “Tickets Resolved Count”.

After that, I recommend removing the second WHEN .. THEN branch regarding the Time dimension and the “Issues due” measure. And you should be all set :slight_smile:.

Let me know how this goes.
Best,

Roberts // support@eazybi.com

3

Hi @roberts.cacus ,

the pleasure is on my side. As always. It is still the same project you already helped me with. :wink:

Great idea, thanks for the hint! Works perfect after replacing the case statement with the iif statement after deleting the second when clause.

Best regards
Sven

1 Like