Time period in Time dimension calculated member

Questions & Answers
,
1

Hi!
I`ve started playing wit eazyBI reports recently, its very powerful tool. Great work EazyBI stuff :slight_smile: Now I need some guidance from your side.
I have tried to define new calculated member with define period of time.
What I am interesting in - I want to display data only from
19 Jun 2019 to 24 Sep 2019 for example.

I was thought that using DateBetween formula could be good idea, but I am not sure how to declare specific date in argument and also it is even possible to use that formula as calculated member.
I get stuck and I need your advice.

DateBetween([Issue].CurrentHierarchyMember.Get(‘Created at’),
‘DateTime’,‘DateTime’)
so DateTime should be as simple as ‘19 Jul 2019’ or it need more complicated syntax?

What I want achieve in final report?
I want have information how many issues was created ( or was moved to ‘Ready’ status) in specifed period of time and also how many of this issues were closed (moved to "Done’ status) to show percentage ratio how many issues is opened and closed during one cadence.
Of course I dont wont to take into count issues which was closed in that period but was created without it.

Thanks in advance!

2

Hi Rasiak,

I had a similar requirement recently, you might want to take a look at this post:

It doesn’t answer your Time question completely, but a step in the right direction for you report.

Cheers.

3

Thanks Doug. I have seen this thread but still I am not able to prepare syntax which I want :frowning:

4

Hi @rasiak,
thanks for the compliments! :slight_smile:
And welcome to our community, I hope you will find the answers you are looking for :slight_smile:

For define period of time you can create a calculated member in Time dimension with DateMembersBetween() function:

Aggregate(
[Time].[Day].DateMembersBetween('Jun 19 2019', 'Sep 24 2019')
)

Then you can use this Time dimension in the Rows and choose measures Issues created and Issues closed from Measure dimension.
​To count how many issues have been transitioned to a particular status, you can create a calculated measure in Measure dimension with tuple and issue history change custom fields (please read related documentation here).
​Formula to use:

​([Transition Status].[To Do], [Measures].[Transitions to status issues count])
​
​If you want to see Issues created and Issues closed in the same time period, you can use adjust formula from example what @Doug already shared, here you can find that example report.
The formula to calculate issues that have been created and closed in the same time period would look like this:

NonZero(
  SUM(
    Filter(
      Descendants([Issue].Currentmember, [Issue].[Issue]),
      DateInPeriod(
        [Measures].[Issue created date],
        [Time].CurrentHierarchyMember)
      AND
        DateInPeriod(
        [Measures].[Issue closed date],
        [Time].CurrentHierarchyMember)
      ),
    [Measures].[Issues closed]
  )
)

The formula about percentage ratio, you can find in that example report and adjust to your needs.

​Here is the example report of how it would look like (without percentage ratio):

Issues-in-time-period
Issues-in-time-period1050×226 10.2 KB

​

best,
Gerda // support@eazybi.com
​
​

5

Hi @gerda.grantina can you please help me here ? I just want to find out the time spent in each status by an issue.
Suppose ex. AAA-101 current status is “Done”. But how many days it spent in “To Do”, “In Progress”, “In Review”, “In QA” like this.
It might be gone in same status again and again 2-3 times.

Thanks!

6

Hi @narendra_kumar_1995,
for that you can use measures and dimensions from Issue change history.
For your case, you could use the Transition status dimension together with measure Days in transition status.

Issues_-Community-_eazyBI
Issues_-Community-_eazyBI2182×1292 569 KB

We have one similar report in our demo account where issues have been filtred by Transition status “Backlog”, but you can export/import definition into your account and change it as you need: https://eazybi.com/accounts/1000/cubes/Issues/reports/61947-issue-days-in-backlog

best,
Gerda

7

Hey @gerda.grantina really it’s helpful and appreciated. A big thanks to you again. Just one query can I round the resulted days. If it’s 0.003 then <1, if it’s 1.0003 then 1. Like rounded result.

@gerda.grantina I can see the days are both working + non-working. What if I only want to show working days only in my report.

Best,
Narendra

1 Like
8

Hi @narendra_kumar_1995,
you can use measure Workdays in transition status for only working days.

best,
Gerda