topic Adding a reference line in QlikView

Adding a reference line

Mon, 12 Sep 2011 10:05:08 GMT

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

Adding a reference line

its_anandrjs — Mon, 12 Sep 2011 10:38:51 GMT

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

Adding a reference line

Mon, 12 Sep 2011 11:01:33 GMT

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

Adding a reference line

swuehl — Mon, 12 Sep 2011 13:12:54 GMT

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

Adding a reference line

Mon, 12 Sep 2011 14:00:59 GMT

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

Re: Adding a reference line

swuehl — Mon, 12 Sep 2011 14:50:16 GMT

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

Adding a reference line

Tue, 13 Sep 2011 08:04:50 GMT

Hi Stefan,

thats exactly what I wanted.

Thank you so much.

... i really must try learning some more about how QV works.

Matt