Hi,
I'm facing a 'challenge' that I hope someone can help me with.
I have a data table that contains EVENT_DATE and QUANTITY, as well as other attributes that are irrelevant here. QUANTITY can only be -1, 0 or 1.
I have a line chart with EVENT_DATE as only dimension and Sum(QUANTITY) as only expression.
Using full accumulation on the expression I can monitor the progression of QUANTITY in time which,
due to the nature of the data, can never go negative.
All is fine if I display the chart for the entire period over which data was gathered, which started
back in 2003. However, if I make a date selection (e.g. 2010 only) the chart will display the progression
relative to 01-01-2010. So what I'm looking for is a way to 'shift' the y-axis values by the sum of QUANTITY
before the start of the selected period (in this example EVENT_DATE < 01-01-2010).
I know how to calculate that value, but the problem is I cannot just add it to the expression, because the chart uses accumulation.
I hope I have explained the problem clearly enough. As an example, consider this data set:
EVENT_DATE QUANTITY
02-02-2002 1
03-03-2003 1
04-04-2004 0
05-05-2005 -1
06-06-2006 1
07-07-2007 -1
08-08-2008 0
09-09-2009 1
10-10-2010 1
11-11-2010 1
12-12-2010 -1
01-01-2011 -1
The sum of QUANTITY at the start of 2010 would be 2, so when plotting the chart for 2010 I would like the line
to commence at y=2 then go to y=3, then to y=4 and finally to y=3 again.
Cheers,
Toine
