Running-Total chart issue [ Solved - I hope]

[cognosjon]

Hi all, hope someone may be able to assist me with this one, my grey matter needs stimulating :o.

I have a line chart that details the number of received items for repair and the number of repaired items. There is also a requirement to display a WIP line which is simply

received items - repaired items

the chart is simply plotted against calendar month on the x axis and the chart must be hard coded to always show the current year.

All is well and good aside from the fact that the WIP line that plots must not start from 0 for each year the chart will ever display. For example the running-total line for January 2008 must continue from the running-total wip line calculated for Decemmber 2007. so December 2007 wip running total may have been 100, then in january wip increased by 10 resulting in a requirement to plot the wip line at 110 for january.

Any suggestions appreciated.

Out of interest I 'm in a positition whereby i can make no changes to either framework model or warehouse.

Thanks for any pointers

[DustPanMan]

Why not use the expression extract(year, getdate()), or the equivalent, to have your year rather than hard-coding it?

[cognosjon]

Dustpanman!! cracking name!!

Sorry when i said hard coded i didn't mean literally. Its filtered using a very similar function to what you detailed. My main issue is how do i get the running total from december to basically become the opening value for jan??

cheers

[cognosjon]

Marvellous think i've just sorted my problem

created two queries.
One containing the filtered year query the second query a carbon copy aside from the date filter.
created a join between the two taking all elements from query 1 and just the running total from query 2.

Thanks to anyone that looked and replied

[DustPanMan]

If you build the running-total at the Query level, will it automatically reset when a new year rolls in then?  It should just continue to show running total.  What you can do to limit the chart is to have a summary filter that limits to only this years data, but show all years that contain data needed in the query.  Make sense?

[cognosjon]

Dustpanman, thanks again for getting back to me,

I think i have a way around my issue but was interested in your summary filter suggestion. I've had a look but can't get it to create what i want.
I've one query that contains everything including the running-total line. I've created a summary filter using the year but all the chart returns is the data for that one year with the running total resetting at the beginning of the year.
Am i missing something here?

[DustPanMan]

On second thought, the data would still be resetting.  I think your two query approach may ne the best option.