Calculating the difference from yesterday to today

[#1]

I have excel files being extracted from a system each day. The files contain the number of orders coming in and going out. For every order I get a 1 in the out and  in row.

I would like to calculate the difference between two days.

sum of all going out orders today - sum of all going out orders yesterday.

I've tried this but can't get it to work properly.

if(peek('MonthNr') <> MonthNr,NewOrder,NewOrder-peek('NewOrder')) as NewOrderDiff

Order by

%ORDERKEY,

CostPlace,

         OrderName,

         Period,

         Date;

Can try and put together an example if it doesn't make sense. Is there something wrong with my order by statement?

[#2]

I believe you can only use order by on resident tables so that might be your problem (but I can't tell from what you posted).  I would also make sure it's sorted before you calculate NewOrderDiff b/c if you don't it's going to screw all your calculations up since you are using peek.

[#3]

Thanks for answering, understand it's a bit theoretical

I've added a zip file with an example.

If you look at it you see that the difference between

20110504 and 20110505 should be  21 - 16 = 5 but in the application it adds up to -2.

[#4]

I would just add a table like this:

NewTable:

LOAD

   Date,

   sum(New) as 'Sum of New'

RESIDENT MembershipsTemp2

GROUP BY Date;

Personally I think the way your table is set up in QV is going to cause some problems.  I'm not really a database expert or anything but you've got a ton of duplicates in there and I just don't really think that is the best way to set it up.  Since I don't really know any background it could be the best way but it looks a little weird to me.  I think all that data should probably be in more than one table but then again, I don't really know for sure.