Hi,
I’m trying to create a report to help with planning, the goal is to be able to compare between parent ticket fixversion and Child ticket fixversion.
If the parent fixversion is “smaller” than the child - need to flag it as an issue to that we can align
This I was able to achieve using this formula:
IIf(Val(ExtractString([Measures].[Dependency Release Date (Child)].Value,‘.M(\d+).’,1)) > Val(ExtractString([Measures].[Issue fix versions].Value,
‘.M(\d+).’,1)),'In trouble ',‘OK’)
If parent ticket is empty and child ticket is not - need to flag it as an issue to that we can align
I don’t know how to flag this, my formula return “OK” but should flagged as trouble.
Fix version have similar format they end with year and Month - XXXX23-M2
Any help would be appreciated
Hi @Souki_Akhdim
You can extend your logic with one more iif function to handle cases where Parent fix version is (no version).
IIf(
Val(ExtractString([Measures].[Dependency Release Date (Child)].Value,'.M(\d+).'
,1)) > Val(ExtractString([Measures].[Issue fix versions].Value,
'.M(\d+).',1)), --validation
'In trouble', --if true
--if false (starts here)
CASE WHEN
[Measures].[Dependency Release Date (Child)]= "(no version)"
AND
[Measures].[Issue fix versions] <> "(no version)"
THEN
'In trouble'
ELSE
'OK'
END -if false ends here
) --iif ends here
Martins / eazyBI
Hi @martins.vanags for some reason when I use this formula, my report disappears.
Note I just updated the syntax.
Does it return empty for all report rows when you try new formula?
Martins / eazyBI
It does return an empty report. I have split the formula in an attempt to debug.
when I use the CASE statement on it own, I don’t see results.
Hi Martins,
It worked, I added another condition.
IIf(
Val(ExtractString([Measures].[Dependency Release Date (Child)].Value,‘.M(\d+).’
,1)) > Val(ExtractString([Measures].[Issue fix versions].Value,
‘.M(\d+).’,1)), --validation
‘In trouble’, --if true
–if false (starts here)
CASE WHEN
[Measures].[Dependency Release Date (Child)]<> “(no version)”
AND
[Measures].[Issue fix versions] = “(no version)”
THEN
‘In trouble’
WHEN
[Measures].[Dependency Release Date (Child)]= “(no version)”
AND
[Measures].[Issue fix versions] <> “(no version)”
THEN
‘In trouble’
ELSE
‘OK’
END --if false ends here
) --iif ends here
I do see a discrepancy in the results, the below should be flagged as In trouble since the child version is superior to the parent’s.
can you please advise ?
Please check separately (in two separate calcualted measures) what is the result for these two formulas:
Val(ExtractString([Measures].[Dependency Release Date (Child)].Value,'.M(\d+).',1))
And
Val(ExtractString([Measures].[Issue fix versions].Value
Likely the first value is not greater than the second, which is why the result is NOT “In trouble” but “OK”
Martins