Set Analysis - Previous Month

[#1]

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)

[Miguel_Angel_Baeyens]

Hi,

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

Above(Sum(myClosingQty)) 

Hope that helps.

Miguel

[#3]

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.

Ian

[Miguel_Angel_Baeyens]

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:

Data:
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.

Miguel