Hello. I have the following custom fields which I’d like to visualize in a bar chart by Quarter-Year:
- Qtr End (Target)
- Qtr End (Actual)
When trying to use “Time” dimension by ‘Quarter-Year’, I run into issues because this is using the issue created date which may not be in the same Qtr End. (e.g. issue was created in November 2018 but Qtr End (Target) = Q1 2019 and Qtr End (Actual) = Q1 2019. This causes my chart to not report metrics accurately.
My question is: Is there a way to create a new date field that I could apply to my horizontal axis which matches the value set in Qtr End (Target) & Qtr End (Actual)? This should then allow for me to graph a distinct count of issues for Qtr End (Target) & Qtr End (Actual), respectively, where this new custom field is the common key.
I hope I got your question correctly. You have two select list custom fields with string values representing quarters with this naming pattern: “QX Year”, for example, Q1 2019, etc.
This pattern matches Time dimension Quarter level member names. You can use quarter names to access members of Qtr End (Target), and Qtr End (Actual) dimensions if you imported them as dimensions.
Here is how to access created issues with Qtr End (Target) using Time dimension on Quarters level:
CASE WHEN [Time].CurrentHierarchyMember.Level.Name = "Quarter"
THEN
NonZero(
( [Measures].[Issues created],
[Qtr End (Target)].[Qtr End (Target)].GetMemberByKey([Time].CurrentHierarchyMember.Name),
[Time].CurrentHierarchy.DefaultMember )
)
END
In case, I got your question incorrectly and you have date custom fields representing quarters Qtr End (Target) and Qtr End (Actual). For example, the last date of the quarter: 31 Mar 2019 for Q1 2019.
You can import any date as a measure. eazyBI should create measure Issues with Qtr End (Target) and Issues with Qtr End (Actual) in this case. They will work with Time dimension by default.
Daina / support@eazybi.com
Hi Daina.
Our custom fields Qtr End (Target) and Qtr End (Actual) are formatted “Year - QX”, unfortunately. I attempted to use your suggested code above but no luck.
The issue I see with the Time dimension currently is that it’s derived off of the Issue Created Timestamp. This creates an issue because when attempting to use the Time dimension to plot “Year QX” on horizontal axis, the metrics are not reporting the way i expect.
For example, I created an issue in Dec 2018 and set Qtr End (Target) = “2019 - Q1” , and Qtr End (Actual) = “2019 - Q1”. When I use Time dimension on horizontal axis, it shows a count of 1 for Q4 2018 instead of Q1 2019.
eazyBI has one Time dimension and the measures make a content what it represents. Default measure Issues created will count issues on time by issue creation date. Measure Issues resolved will count resolved issues on time by issue resolution date.
If you have a date custom field Qtr End (Target) (I would suggest considering using dates instead of selection fields for quarters) you can import this date custom field as a measure and it will create set of measures, including Issues with Qtr End (Target). This measure will show you a count of issues on time based on this date.
Time is an important dimension in eazyBI. My colleague Roberts had a topic about Time dimension in eazyBI in our community days.
Daina / support@eazybi.com