Compare two custom fields with native field

Hello, I´m looking for a solution how to compare two custom fields and one native field ( Fix version). There is an option to import those custom fields as Dimension or as Property.

The goal is to compare those two custom fields and one native field (Fix version) between each other.
Expected output after comparison is text field/string

Below is table with values which shows possible options and the yellow highlighted column shows the output

Any idea what needs to be done ?
Thanks in advance for any response

Hi,

Welcome to the eazyBI community!

In eazyBI you can compare fields using MDX (and create calculated measures for output).
If you expect to compare dates, you can use regular expressions to extract and parse the date from string value and then use date functions (like “DateCompare”, “DateBetween” etc) to compare dates and return the custom string as output.

If you want to match full string vs another string, you could use Matches function.
As I understand it would work only at Issue dimension issue level here as only there you would see custom field imported as property.
Assuming that you want to compare issue Fix Version with two custom fields imported as property the question would appear - what to do if an issue has more than 1 fix version.

I guess you could reach out to support@eazybi.com with more details on this use case - how exactly do you want to display results (what dimensions used in rows/columns, what dimensions used in page filters) in your report.

Martins / eazyBI support

Hi Martin,
How to Compare Multiple date fields and find the latest date among them and output string based on latest date. Example: PCR received date, PCR responded date and Agreed requirements date .
if PCR recevied date is latest date compared to other date fields then i want status to be ‘PCR received’.
if PCR responded is latest date compared to other dates than i want status as ‘PCR Responded’.
if Agreed requirements date is latest compared to other dates than i want status as ‘Reqs Agreed’
Please assist

@lakshmi

Try using the Max function when creating new calculated measure:

TimestampToDate(
Max(
  {[Measures].[Issue PCR received date],
   [Measures].[Issue PCR responded date],
   [Measures].[Issue Aggreed requirements date]},
  DateToTimestamp([Measures].CurrentMember)
)
)

That should return the latest date from 3 properties (date picker field values imported as properties for Issue dimension members)
Just make sure you save the new calculated measure with the correct format.

Martins / eazyBI

1 Like

Thank you very much @martins.vanags . It worked :slight_smile:

@martins.vanags . how could I do this with date compare? I want to print overdue when BOTH issue due date AND Issue Target End Date are overdue. this is what I have for one date but i cant get two to work?

CASE WHEN 
DateCompare("today",[Measures].[Issue due date])>0
THEN
"Overdue"
END

@joeyperf
Make sure you import “Targe End date” as property from the import options page first.
Then use this formula for your calculated measure:

CASE WHEN 
DateCompare("today",[Measures].[Issue due date])>0
AND
DateCompare("today",[Measures].[Issue Target End Date])>0
THEN
"Overdue"
END

Martins / eazyBI

1 Like