Cumulative Field distorting data

[#1]

Hi guys,

date	Tally
01/02/2010	1
02/02/2010	2
03/02/2010	3
04/02/2010	5
05/02/2010	38
06/02/2010	56
07/02/2010	65
08/02/2010	78
09/02/2010	243
10/02/2010	243
11/02/2010	243
12/02/2010	245
13/02/2010	249

I have a table with similar figures as above. The Tally is a cumulative count of the data eg for the 10/02/2010 there are no alerts. Using max(Tally) I have constructed a number of charts as this is the actual number of overall alerts.

However, we have a calendar option where people can choose their dates. If they pick 10th Feb, we would want the chart to have a 0 as no alerts occurred on this day. Currently it shows 243.

Is there a way possibly using the 'previous' function in which it would show the correct data for each individual day?

Thanks.

[#2]

Hi,

you can extract daily values with something like this

load *,
Tally-num('0' & peek('Tally',-1)) as daily
INLINE [
date, Tally
01/02/2010, 1
02/02/2010, 2
03/02/2010, 3
04/02/2010, 5
05/02/2010, 38
06/02/2010, 56
07/02/2010, 65
08/02/2010, 78
09/02/2010, 243
10/02/2010, 243
11/02/2010, 243
12/02/2010, 245
13/02/2010, 249
];

Alex

[#3]

Thanks,

I think I am going to go down the road of creating another field like

previous(Tally) as PreviousTally

, I think this should work? However, one problem i am still having is that I have another field - SerialNumber. When I try to load from a QVD and put : order by SerialNumber at the end, it causes a load failure.

Any suggestions to why this may be the case?

Thanks,

[michael_anthony]

Might be wrong, but think I read that can only use Order By with a LOAD ... RESIDENT table. Using on a load from QVD or other source isn't allowed.