4 Replies Latest reply on Jan 6, 2015 7:09 AM by Sam Ainscow

# How do I create an Ideal or Optimal Trend Line?

Hello,

I'm new with Tableau and I'm attempting to reproduce some SCRUM reports that are natively produced within Microsoft TFS.   I'd like to create an ideal trend line, as seen below in figure 1 (The line basically travels from the initial data point to 0 over a period of time).  I've managed to do everything else in Tableau as seen in figure 2, but I really need that Ideal Trend Line to make these charts clear for the user.

Thanks for any help or ideas.

Figure 1

Figure 2

• ###### 1. Re: How do I create an Ideal or Optimal Trend Line?

How about something like the attached, that uses the formula:

(LOOKUP(SUM([Amount]),FIRST())/(SIZE()-1))*LAST()

• ###### 2. Re: How do I create an Ideal or Optimal Trend Line?

I gave this a whirl, it didn't seem to work properly with the data, It created two trend lines (one for each state[color]).  Thanks for the attempt on it.

• ###### 3. Re: How do I create an Ideal or Optimal Trend Line?

I've come up with the solution for this.  It involves a few calculated fields.

Create a "Date" calculated field that timeboxes the historical data within the confines of your sprint (this is required for creating the charts so that the x-axis fits between the sprint start and end day.

Date = IF [DateSK]<=[Microsoft_VSTS_Scheduling_StartDate]Then [Microsoft_VSTS_Scheduling_StartDate] ElseIF [DateSK] > [Microsoft_VSTS_Scheduling_FinishDate] Then NULL Else[DateSK] End

Then create these fields to produce the trend line:

First = if index()=1 then sum([Microsoft_VSTS_Scheduling_RemainingWork]) else NULL end

Last = lookup( sum([Microsoft_VSTS_Scheduling_RemainingWork]),last() )

window_max_date = window_max( max([Date]))

Burndown =

if index()=1 then sum([Microsoft_VSTS_Scheduling_RemainingWork])

elseif attr([Date])= window_max( max([Date])) then 0

else null

end

And your chart should look something like figure 1 below.  Unfortunately, this still doesn't solve my problem completely.  If the data for sprint is partially completed, ie., the sprint is in progess, the chart x-axis shortens as there isn't data for the remaining portion of the sprint yet. As a result, this trend line shortens to match the existing data, producing an inaccurate ideal trend.  Ideas?

Figure 1

• ###### 4. Re: How do I create an Ideal or Optimal Trend Line?

Hi Daniel

I am trying to do exactly the same as you were here. Would you be able to share a packaged workbook of yours to help me get started?

Thanks

Sam