Hi there!
I’m struggling with creatng a report that shows how status changed troughout the previous week.
If I use this configuration:
Column Time (Previous week) Measures (issue status)
Row
Issue
I’ve got “In progress” status on those issues wich were in “To do” on a specific date.
After flipping hundreds of doc pages I stopped at Issue history measure…but it only do counting. The result is number. I got this:
Is there any way to simply show the status that was at a specific time member?
Hi @KAbdulina,
Thanks for posting your question!
I suggest using the “Issues history” measure and Transition Status dimension for your report.
“Issues history” measure counts issues at the end of the selected Time dimension period.
You can read more about “Issues history” measure in our documentation here: Import issue change history and Transition Status dimension here: Import issue change history
You can also check out our sample report for Unresolved Issues by Statuses over Time: Unresolved issues by statuses over time
To illustrate the case, I have taken an example of our sample report (see the screenshot below).
You can see that “Issues history” returns either a blank value, 0 or 1.
-
The result is returned 1 where the Issue is in a specific status at the end of the selected Time period.
-
It is returning 0 if the Issue was in the status on selected Time period but has transitioned into another status.
-
If no results are returned, we can conclude that the Issue was not in that particular status on the selected Time period.
In the screenshot below, you can see that Issue DB-4 is in status “Review” at the end of the week of 22nd Feb 2021. A 0 in “To Do” column shows us that this Issue was in this status on this date but has been moved out of it by the end of that date.
If necessary, you can apply “Filter rows” → “>” (bigger than) → 0 to only see the Status of an Issue at the end of the chosen Time period.
I hope this helps!
Best,
Marita // support@eazybi.com
Hi @Marita_Norvele
I’ve managed to do a step further with my formula. So, for now I have what I need with this formula:
CASE WHEN
not IsEmpty([Issue].CurrentHierarchyMember.Get('Status updated at'))
THEN
Order(
Filter(
[Transition Status].[Transition Status].Members,
([Time].[Week].CurrentDateMember,
[Measures].[Issues history])>0
),
[Measures].[Transition from status last date],
BDESC
).item(0).getCaption
END
This give me a nice table:
However, my final goal is to show issues logged by user from team. So when I add “Logged by Team” and “Logged by” dimensions to rows I got this table with
#null values of statuses:
I think that is because I didn’t specify those demensions in formula. Unfortunately, I have no idea how to fix it. So any help is highly appreciated.
Thanks in advance,
Ksenya.
Updating my previous code:
CASE WHEN
not IsEmpty([Issue].CurrentHierarchyMember.Get('Status updated at'))
THEN
Order(
--Order Set_Expression
Filter(
--Filter Set_Expression
([Transition Status].[Transition Status].Members),
--Filter Logical_Expression
([Logged by Team].DefaultMember,
[Logged by].DefaultMember,
[Time].[Week].CurrentDateMember,
[Measures].[Issues history])>0
),
--Order Value
[Measures].[Transition from status last date],
--Order Symbol
BDESC
).item(0).getCaption
END
So for now I have desired result. Thanks for the eazyBI Documentation and training videos I understood that if you have to tell MDX to omit some dimension, you have to add DefaultMember to the formula.
1 Like
