Projections that hit 0 between periods?

Questions & Answers
1

Hi all,

I have a report that tracks bug trends over the past month and projects the trend over the next 3 months based on the rate of closure. The report is currently looking at time by week, and works as expected with one exception; if the projection hits 0 in the middle of a period, 0 doesn’t register on the table (i.e., the last row is >0 and then the remaining rows are blank). This means the projection doesn’t plot on the line, which looks weird.

image
image1609×541 51 KB

Is there any way to fix this short of making the report daily? This is my current calculation for the projection:

CASE WHEN
  DateCompare(now(),
    [Time].CurrentHierarchyMember.StartDate) < 0
AND
  (([Time].CurrentHierarchy.Level.CurrentDateMember,
  [Measures].[A Bugs Remaining])
* DateDiffDays([Time].CurrentHierarchyMember.StartDate,
   [Measures].[Predicted Completion Date (A Bugs)])
/ DateDiffDays(Now(),[Measures].[Predicted Completion Date (A Bugs)])) >= 0
THEN 
([Time].CurrentHierarchy.Level.CurrentDateMember,
  [Measures].[A Bugs Remaining])
* DateDiffDays([Time].CurrentHierarchyMember.StartDate,
   [Measures].[Predicted Completion Date (A Bugs)])
/ DateDiffDays(Now(),[Measures].[Predicted Completion Date (A Bugs)])
WHEN
DateInPeriod(
  'Today',
  [Time].CurrentHierarchyMember
)
THEN
NonZero([Measures].[A Bugs Remaining])
END

Any insight on this would be helpful, thanks!

2

Hi @rbelmont,

You couold try to use another “when” case that would convert numbers smaller than 0 to 0

CASE WHEN
  DateCompare(now(),
    [Time].CurrentHierarchyMember.StartDate) < 0
AND
  (([Time].CurrentHierarchy.Level.CurrentDateMember,
  [Measures].[A Bugs Remaining])
* DateDiffDays([Time].CurrentHierarchyMember.StartDate,
   [Measures].[Predicted Completion Date (A Bugs)])
/ DateDiffDays(Now(),[Measures].[Predicted Completion Date (A Bugs)])) >= 0
THEN 
([Time].CurrentHierarchy.Level.CurrentDateMember,
  [Measures].[A Bugs Remaining])
* DateDiffDays([Time].CurrentHierarchyMember.StartDate,
   [Measures].[Predicted Completion Date (A Bugs)])
/ DateDiffDays(Now(),[Measures].[Predicted Completion Date (A Bugs)])
WHEN
DateInPeriod(
  'Today',
  [Time].CurrentHierarchyMember
)
THEN
NonZero([Measures].[A Bugs Remaining])
WHEN
  (([Time].CurrentHierarchy.Level.CurrentDateMember,
  [Measures].[A Bugs Remaining])
* DateDiffDays([Time].CurrentHierarchyMember.StartDate,
   [Measures].[Predicted Completion Date (A Bugs)])
/ DateDiffDays(Now(),[Measures].[Predicted Completion Date (A Bugs)])) < 0
THEN 0
END

It might also possible to put it as “ELSE” like this

CASE WHEN
  DateCompare(now(),
    [Time].CurrentHierarchyMember.StartDate) < 0
AND
  (([Time].CurrentHierarchy.Level.CurrentDateMember,
  [Measures].[A Bugs Remaining])
* DateDiffDays([Time].CurrentHierarchyMember.StartDate,
   [Measures].[Predicted Completion Date (A Bugs)])
/ DateDiffDays(Now(),[Measures].[Predicted Completion Date (A Bugs)])) >= 0
THEN 
([Time].CurrentHierarchy.Level.CurrentDateMember,
  [Measures].[A Bugs Remaining])
* DateDiffDays([Time].CurrentHierarchyMember.StartDate,
   [Measures].[Predicted Completion Date (A Bugs)])
/ DateDiffDays(Now(),[Measures].[Predicted Completion Date (A Bugs)])
WHEN
DateInPeriod(
  'Today',
  [Time].CurrentHierarchyMember
)
THEN
NonZero([Measures].[A Bugs Remaining])
ELSE 0
END

I hope this helps,
Marilou

3

@Marilou thanks, this definitely addresses my core issue of the calculations not hitting 0 in a period. However, this introduces a new (admittedly minor) issue where the projections continue to chart zeroes for the the remainder of the timeline. Preferably, once the projection hits 0 it would just stop charting forward (i.e., after 0 there would just be blank rows that aren’t graphed). Thoughts on how I might address that?

image
image735×456 33.2 KB

Thanks!

4

Hi @rbelmont,
I tried to change the case when “<0 = 0” "to <0=’ ’ " to have empty values, but that brought me back to the initial “floating end of line” issue.

I tried to the “<0” to “<next data point-1” (ex: <15=’ ') which makes the line complete, but it then goes under 0 and you would need to manually adjust the query everytime the prediction changes, which is undesirable.

image
image752×374 12.2 KB

I’ll continue to try a few different things, but I don’t know if it can be done.

I wish I could be of more help :confused:
Marilou

5

Worth a shot, every tool has limitations :slight_smile: I appreciate you digging into it for me!

6

Hi rbelmont and Marilou,
You could try adding the following code to show 0 only for the next period after the projection hits 0 (reaches the predicted completion date):

WHEN
DateCompare([Time].CurrentHierarchyMember.StartDate,
[Time].CurrentHierarchy.Level.DateMember([Measures].[Predicted Completion Date (A Bugs)]).NextStartDate)=0
THEN
0

I hope this helps.

Best,
Ilze

1 Like