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

    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:

                  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.