3 Replies Latest reply: May 24, 2011 10:15 AM by Trent Jones

# Calculating the difference from yesterday to today

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?

• ###### Re: Calculating the difference from yesterday to today

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.

• ###### Calculating the difference from yesterday to today

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.

• ###### Re: Calculating the difference from yesterday to today

I would just add a table like this:

NewTable: