Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I have a graph showing actuals against projects accumulated over time.
on the x-axis I have set the static min to be the start_date of the project
the static max to be the end_date of the project.
I need to add a reference line which starts at a value of 'BUDGET_DAYS' (the project's budget) at the start of the x-axis
and reduces uniformly overtime to a value of 0 at the end of the x-axis (the static max)
I was thinking that the value could be worked out on a daily basis, by working out the BUDGET per day (BUDGET/(END_DATE-START_DATE))
and then reducing it by this amount for each day..
its just that I have NO idea how to express that.
fields I have are:
PROJECT_CODE
START_DATE
END_DATE,
BUDGET_DAYS
Can anyone suggest how I might achieve that?
Regards,
Matt
Hi Matt,
you are right, there is an easier solution.
I added an expression like:
=if(TRANSDATE = min(total TRANSDATE),
sum(total BUDGET_DAYS)+ sum(total BUDGET_DAYS)/(min(total START_DATE)-max(total FINISH_DATE))*(min(total TRANSDATE)-min(total START_DATE)),
if(TRANSDATE=max(total TRANSDATE),
sum(total BUDGET_DAYS)+ sum(total BUDGET_DAYS)/(min(total START_DATE)-max(total FINISH_DATE))*(max(total TRANSDATE)-min(total START_DATE))
))
to calculate two sampling points for your reference line at the min and max TRANSDATE. Then just add a trendline to get a line from start till end of your x-axis.
See attached.
Is this what you wanted?
Regards,
Stefan
Hi,
As i understand the things that you want to show on chart for this i suggest you to use two reference lines
Min( BUDGET_DAYS ) and Max( BUDGET_DAYS ) and use two reference lines for that.
HTH
Regards
Anand
Hi Anand,
Thanks for replying.
Unfortunately I need only 1 line.
I have attached a qvw to my original post.
You can see the graph I am working on its a makeshift burn-down chart.
The reference line I need would go from 22.0004444 (as seen in one of the text boxes) at 27/06/2011 on the x-axis
DOWN to 0 on the x-axis at a date of 26/10/2011 (corresponding with the finish date of the selected project -D03271-A)
i need a straight line going diagonally down from 22.0004444 to 0 across those two dates.
Thanks in advance,
Matt
Matt,
as far as I see, your TRANS_DATEs are not including the start and (projected) end dates of the projects.
If they would, I think a simple
=if(TRANSDATE=MIN(START_DATE),sum(total BUDGET_DAYS),
if(TRANSDATE=max(END_DATE),0))
would be enough, adding two data points at the TRANS_DATE extrema.
Do you think it is possible to add those dates to your model?
Regards,
Stefan
I'm not sure how I could do that.
The transdates are from the transactional tables where resource time-entries are stored - a project start date might sometimes be before any time is booked.
I was hoping that using a continuous axis would have provided a reference point for a line
my axis starts and ends with the project start/finish date
Hi Matt,
you are right, there is an easier solution.
I added an expression like:
=if(TRANSDATE = min(total TRANSDATE),
sum(total BUDGET_DAYS)+ sum(total BUDGET_DAYS)/(min(total START_DATE)-max(total FINISH_DATE))*(min(total TRANSDATE)-min(total START_DATE)),
if(TRANSDATE=max(total TRANSDATE),
sum(total BUDGET_DAYS)+ sum(total BUDGET_DAYS)/(min(total START_DATE)-max(total FINISH_DATE))*(max(total TRANSDATE)-min(total START_DATE))
))
to calculate two sampling points for your reference line at the min and max TRANSDATE. Then just add a trendline to get a line from start till end of your x-axis.
See attached.
Is this what you wanted?
Regards,
Stefan
Hi Stefan,
thats exactly what I wanted.
Thank you so much.
... i really must try learning some more about how QV works.
Matt