Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

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?

3 Replies
Not applicable

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.

Not applicable

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.

Not applicable

Re: Calculating the difference from yesterday to today

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.

Community Browser