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
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.
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
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.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
@martins.vanags I need help with the date compare function. I have two custom date field. One is Commit date and other one is Revised Complete Date. I want to show the new Revise Complete date value. If the Revised Complete date value is empty then I want to show the Commit date value.
Try using IsEmpty() in the CASE statement or IIF() function
CASE WHEN
Not IsEmpty([Mesasures].[Issue Revised Completed Date])
THEN
[Mesasures].[Issue Revised Completed Date]
WHEN
IsEmpty([Mesasures].[Issue Revised Completed Date])
THEN
[Mesasures].[Issue Commit Date]
END
I want to build a bar report that show total Epic with the custom measure Metric Date but it is not showing the data.
Column: Request Type (custom field)
Page: Measures with the following properties: Metric Date (custom measure definition)
Row: Time dimension and Selected Month at All hierarchy level members.
The calculated measure you created returns a property for “Issue” (either Revised Completed date or Committed date). This measure would return results only if you select the particular issue in your report which you don’t.
If you want to calculate the count of issues, you woul need to search for the measure “Issues with ” instead.
You could create the calculated measure as follows:
CoalesceEmpty([Measures].[Issues with Revised Completed Date],[Measures].[Issues with Commit Date])
It will return the count of Issues with Revised completed date for each time period in the report, and Issues with commit date for periods where there are no Revised completed date issues.
Thank you for the quick response. I created the calculated measure as you have showed but the report is showing both Revised and Commit date.
I want the report to show the Revised complete date when value is not empty and show the Commit date when Revised complete date is empty.
Try this formula (select “Ingteger” format manually) for your calcualted measure:
[Measures].[Issues with Revised Completed Date]
+
Sum(
Filter(
DescendantsSet([Issue].CurrentMember,[Issue].[Issue]),
IsEmpty(
[Measures].[Issue Revised Completed Date]
)
AND
DateInPeriod(
[Measures].[Issue Commit Date],
[Time].CurrenthierarchyMember
)
),
CASE WHEN
[Measures].[Issues with Commit Date]>0
THEN
1
END
)
Thank you for the updated. The updated calculation is working fine. It looks like the Time dimension didn’t pick up the right field to show the correct date from the calculation. Please see the screenshots.
Rows: Time
Column: Measures
Issue, Issue Epic Name, Issue Commit Date (Custom date field), Issue Revised Complete Date (Custom date field), Metric Date (custom calculation measure), Metric Date 02232023 (latest custom calculation measure I received from you).
@Aaron
Issue 7161 KHV is counted to Dec 2022 because the Commit date is on Dec 23, 2022.
Would you expect it to be counted for different month instead?
I would expect it to pick up the Revised Complete date for a new due date of Dec 23, 2025. My logic was to configure the report to show the Commit date if there is no Revised Complete date and show the Revised Complete date if there is Revised Complete date value.
Sum(
Filter(
DescendantsSet([Issue].CurrentMember,[Issue].[Issue]),
DateInPeriod(
CoalesceEmpty([Measures].[Issue Revised Completed Date],[Measures].[Issue Commit Date]),
[Time].CurrenthierarchyMember
)
),
CASE WHEN
NOT IsEmpty([Measures].[Revised Completed Date])
THEN
CASE WHEN
[Measures].[Issues with Revised Completed Date]>0
THEN
1
END
ELSE
CASE WHEN
[Measures].[Issues with Commit Date]>0
THEN
1
END
END
)
Don’t forget to manually select the “ingeger” format for this calcualted measure. eazyBI won’t be able to guess the right output format automatically.
