3 Replies Latest reply: Jan 31, 2012 7:45 PM by Miguel Angel Baeyens de Arce RSS

    Set Analysis - Previous Month

      I am trying to use Set Analysis to calculate a field based on that field's value in a previous month. I have 4 fields: myCompany, monthID, myMonth, myClosingQty. I would like to calculate the OpeningQty - which is the closingQty of the Previous month. I have used a monthID to minimise calculation errors based on different date settings on different systems as well as unforseen date calculation errors. Attached is a sample of what I am trying to achieve - Opening Qty in this sample all has to move "one record down".
      The Set Analysis I am having problems with is:
      = Sum({$<MonthID = {$(=Max(MonthID)-1)}>}myClosingQty)
        • Re: Set Analysis - Previous Month
          Miguel Angel Baeyens de Arce



          If you use a straight table you don't need set analysis and you can use the inter record functions. In your chart, remove one of the two month fields, and leave the opening quantity expression as




          Hope that helps.



            • Re: Set Analysis - Previous Month

              Hi Miguel,

              Thank you for your answer. Unfortunately I cannot use inter record functions. The displayed app was just to show a sample. The "real" app does not use a straight table.

              Sorry about the confusion.


                • Re: Set Analysis - Previous Month
                  Miguel Angel Baeyens de Arce

                  Hi Ian,


                  Regardless the type of chart you are going to use, if you always want the previous month amount as the opening for this month, you can get that amount in the script using the Previous() function. According to your script (I know, this is only an example, and you are not loading from INLINE but here's the idea) doing a load sort by month you can always store in a different field the quantity of the previous month, then sum this in the chart:


                  LOAD *, Previous(myClosingQty) AS myOpeningQty INLINE [
                      myCompany, monthID, myMonth, myClosingQty
                      ACME, 123, 31/01/2011, 200
                      ACME, 124, 28/02/2011, 300
                      ACME, 125, 31/03/2011, 400
                      ACME, 126, 30/04/2011, 500
                      ACME, 127, 31/05/2011, 600


                  The new field myOpeningQty has the value for the previous month, so now you can use this field in the Sum(). You will need to do two loads (or even more) if your select does not return values ordered by month.


                  Hope that gives you the idea.