OR/AND Condition for empty custom field

Questions & Answers
, , ,
1

Dear madam and sir,

please how to convert this jql

  1. status != Open AND (CustomField1 is EMPTY OR CustomField2 is EMPTY)

  2. ( ( status in ('In Progress', Resolved) ) OR (status=Closed AND Resolution=\"Won't do\") ) AND type = Task

in to easyBI measure?

Thanks in advance

Bodi

2

Hi @Bodi,

There are several approaches how to converting those two JQL queries to the calculated measures:

  1. tuple construction (see documentation: Calculated measures - Tuples)
  2. or combination of Filter() and aggregate function Sum() (see documentation: Calculated measures - Aggregate measures).

My recommendation is to use the tuple construction, as it is a faster calculation and would not impact report performance much.
I will start with the second as that expression is simpler.

  • 2nd JQL expression:
    ( ( status in (‘In Progress’, Resolved) ) OR (status=Closed AND Resolution="Won’t do") ) AND type = Task

    You can convert this expression to calculate measure by adding up three tuples because tasks could fall into one of the three statuses:

    ([Measures].[Issues created],
[Issue Type].[Task],
[Status].[In Progress])
+
([Measures].[Issues created],
[Issue Type].[Task],
[Status].[Resolved])
+
([Measures].[Issues created],
[Issue Type].[Task],
[Status].[Closed],
[Resolution].[Won't Do])

  • 1st JQL expression: status != Open AND (CustomField1 is EMPTY OR CustomField2 is EMPTY)

    This is a bit more complex expression because it has not matches (!=) criterion. eazyBI likes when criteria are described from the opposite - what matches.
    Think of Venn diagrams that might help you to model the logic of which tuples should be added up and subtracted to remove duplicates.

     --all statuses where one of the custom fields are empty
 (
   ([Measures].[Issues created],
   [Status].CurrentHierarchy.DefaultMember,
   [CustomField1].[(none)])
   +
   ([Measures].[Issues created],
   [Status].CurrentHierarchy.DefaultMember,
   [CustomField2].[(none)])
   --subtract issues where both custom fields are empty as previous tuples already cover those
   -
   ([Measures].[Issues created],
   [Status].CurrentHierarchy.DefaultMember,
   [CustomField1].[(none)],
   [CustomField2].[(none)])
 )
 --subtract Open issues where one of the custom fields is empty
 -
 (
   ([Measures].[Issues created],
   [Status].[Open],
   [CustomField1].[(none)])
   +
   ([Measures].[Issues created],
   [Status].[Open],
   [CustomField2].[(none)])
   --subtract issues where both custom fields are empty as previous tuples already cover those
   -
   ([Measures].[Issues created],
   [Status].[Open],
   [CustomField1].[(none)],
   [CustomField2].[(none)])
 )

    Use the opening and closing brackets to correctly apply the + and - signs. The same mathematical rules as in school.

Please see this training video on: Filter Data: How to Translate JQL to eazyBI. This video also covers another option using a combination of Filter() and aggregate function.

Best,
Zane / support@eazyBI.com

3

Hi @zane.baranovska ,

thks^3 :smiling_face: , it works fine with tuples
The video is also good.

Best
@Bodi

1 Like