Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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.
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.
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.