I am trying to create a report that shows the number of jira issues in various statuses. I want a week over week report status as of “Monday”. I have the “first day in the week” defined as Sunday
I have tried to use the “Current Week” and “Previous Week” weekly members of the time dimension, but that does not work, because the “Previous Week” is basically showing status as of the previous “Saturday”, aka the end of the week. So, if someone were to look at the report on Monday, they don’t see any change in issue counts in the past week. I am trying to create calculated members representing Monday of this week and Monday of last week. I have found that I can used the following to calculate data on a Monday. However they all seem to be reporting the same status. Plus, I cannot find a definition in MDX for the definition of “Monday”, “This Monday”, nor “Last Monday”
— And I can use
– And I can use
So, can someone help me to understand the proper “Monday” construct to use? I believe that then I could use the lag function on “7” days to get the proper previous Monday, assuming that it is not lag 8.
Also - is there a better way to do this?
Go to the “Data Source” options, you can specify when the first day of the weeks starts:
Yes, I have the week starting on Sunday.
I have gone with “Monday” defined as
And “Last Monday” defined as
Not sure if there is a better way. Unfortunately, this loses the default date information that is printed at the bottom of the charts when you use the delivered Weekly calculated members of “Current Week” and “Previous Week”.
As an alternative, if you have the “Week” members selected in the Time dimension, you can create a new calculated measure that will get the number of issues in each status as of each Monday of the week:
As you have set Sunday as the start of the week in the eazyBI import settings, it is necessary to add one day to each week’s start date to get Monday. The DateAddDays() function takes care of that.
One other thing I would recommend for this to work as expected - add missing date members to the Time dimension date range - https://docs.eazybi.com/eazybijira/analyze-and-visualize/create-reports#Createreports-AddmemberstoTimedimension.
The report then would look similar to the one below:
You can read more about the functions used in eazyBI calculated measures and members on the documentation page - https://docs.eazybi.com/eazybijira/analyze-and-visualize/calculated-measures-and-members/mdx-function-reference.
Roberts // eazyBI support
Thank you Roberts. I will research this alternative solution as well. Thank you also for the additional references.