Trend lines based on specific period of time instead of visible rows

Questions & Answers
1

I want to create a Trend line that is based on a chosen amount of time but display more time in my chart. For example, I want my Open Issue Trend line to be based on the previous 8 time periods, but I want to display the last 24 time periods (here weeks).

The calculated trend line is based on visible rows, is there a way to change it’s based on the last 8 time periods?

Thanks

2

Hi @Marilou!

Yes, you could create a calculation where you define the period over which you wish to calculate the trend. You can either copy the added measure formula and then modify it or create a new one from scratch with LinRegPoint(…) function.

Here is an example for the Open issues trend based on the last 8 weeks (when Time.Weekly hierarchy is used on rows):

  LinRegPoint(
    -- Numeric expression output x
      DateToTimestamp([Time].CurrentHierarchyMember.StartDate),
    -- Set expression - input period
    [Time.Weekly].[Week].DateMembersBetween("8 weeks ago", "today"),
    -- Numeric expression input y
    [Measures].[Open issues],
    -- Numeric expression input x
      DateToTimestamp([Time].CurrentHierarchyMember.StartDate)
  )

Lauma / support@eazybi.com

1 Like
3

Hi Laura!

Following up on this, in this:

[Time.Weekly].[Week].DateMembersBetween("8 weeks ago", "today")

Is Today included in the calculation or Excluded.

Thansk!

4

Hi @Marilou,

Yes, the today is the to_date parameter. The to_date is included in the calculation. See more DateMembersBetween.

Lauma / support@eazybi.com

1 Like
5

I need help on this, please. I’ve added the custom measure and the calculation is correct, but I can’t get it to extend further than the built in linear trend calculation. I’ve tried changing it to “40 weeks from now,” but no additional dates were added on the x-axis. Here’s my code:

 LinRegPoint(
    -- Numeric expression output x
      DateToTimestamp([Time].CurrentHierarchyMember.StartDate),
    -- Set expression - input period
    [Time.Weekly].[Week].DateMembersBetween("8 weeks ago", "40 weeks from now"),
    -- Numeric expression input y
    [Measures].[Cumulative Story Points resolved],
    -- Numeric expression input x
      DateToTimestamp([Time].CurrentHierarchyMember.StartDate)
  )
6

Hi @camgray!

The input period is only for calculating the trend slope. It does by default extend over all Time members you have on rows.
By default, eazyBI is adding members to the Time dimension only if there is some activity on that date. Future dates often are not added as the only action that usually is in the future is the Issue due dates or Sprint end date. If you would like to add projections in the future (or fill in gaps from the past), you can add members to the Time dimension manually. See details here Time dimension.

Lauma / support@eazybi.com