topic Calculating the difference from yesterday to today in QlikView

Calculating the difference from yesterday to today

Mon, 23 May 2011 00:44:12 GMT

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

Mon, 23 May 2011 21:03:04 GMT

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

Tue, 24 May 2011 00:16:16 GMT

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

Tue, 24 May 2011 14:14:36 GMT

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.