topic Advice on creating a 'burn-down' chart in QlikView

Advice on creating a 'burn-down' chart

Sun, 11 Sep 2011 23:15:49 GMT

Hi all,

I need to be able to create a line graph that will act as a 'burn-down' showing spend against budget of a project.

I have the data I need:

Project Budget Days

Project Start Date

Project End Date

Project Transactions (time entries in hours)

I need to create a graph that has the budget_hours as the maximum value on the y-axis

The time-entry date (Year/month) along the x-axis

I need the graph to show' Budget Hours' MINUS 'Total time booked'

and be cumulative so the line goes from the budget-0 down to 0 over time...

Has anyone acheived this? or have any examples?

In my expression I am just using SUM(BUDGET_DAYS) - SUM(QUANTITY/7.5)

(7.5. to get a value from hours to days)

but the graph burn-down line is not going down, which I woudl expect.

I attach it for reference.

Ideally, this would work when the user selects a 'program' (which will contain several projects and sum everything)

this is why I have chosen to use a SUM of Budget Days.

Thanks in advance,

Matt

Advice on creating a 'burn-down' chart

swuehl — Sun, 11 Sep 2011 23:33:39 GMT

Hi Matt,

on the budget burn down expression, set accumulation from full to no accumulation and use this as expression:

=sum(BUDGET_DAYS)-rangesum(above(sum(QUANTITY)/7.5,0,rowno()))

(you want to accumulate only the burned down Quantity, not the Budget days offset, right?)

Regards,

Stefan

Advice on creating a 'burn-down' chart

Sun, 11 Sep 2011 23:48:55 GMT

Hey, thanks Stefan thats brilliant!

I'm really not that familiar with many of the functions within QlikView such as rangesum()

and yes, you are right, I do ONLY want to accumulate the burned down quantity 🙂

To expand on this,

If I was to click on a 'program' in a list box I will add, it will have several projects in the resulting data set.

Is there a way I can get the total budget_days (SUM) across all projects returned?

at the moment, this value changes, due to the projects not all having transactions in all months.

I'd like to somehow detach the SUM(BUDGET_HOURS) from the rest of the data, and just use that figure as a reference to subtract the accumulated quantity...

if that makes any sense.. 🙂

this way, I can use the burndown chart to show a single project or an entire program.

Advice on creating a 'burn-down' chart

swuehl — Mon, 12 Sep 2011 09:13:50 GMT

I just had a very short look to your data model, maybe just using

=SUM(total BUDGET_DAYS)

to calculate budget should be enough. (I checked with some programmes and projects, but not sure if I understood you correctly).

Besides that, I would probably enable "supress when value is Null" on dimension tab for the chart.

Regards,

Stefan

Advice on creating a 'burn-down' chart

Mon, 12 Sep 2011 09:26:16 GMT

Thats great, thank you SO much for this.

Out of interest (and I might post this as another discussion) DO you know if it is possible to manipulate the x-axis?

What I'm thinking is to create a guideline for a uniform burn-down.

basically, a line going from the SUM(Total BUDGET_DAYS) down to zero

Starting with the x-axis = Min(START_DATE)

and going down to MAX(FINISH_DATE)

Where I can set the max of the y-axis, i'm not sure how possible it is to do it on the x-axis...

Thanks,

Matt