Suggestion on Scripting Backlog Transaction (Date Related)

[#1]

Hi guys,

Hope you can suggest a good scripting on my requirements below:

Data               : Orders with date ranging from 2010-Present

Requirement    : Backlog report by month

year-month - no of orders

2010-1     -     10

2010-2     -     23              (should be the sum of 2010-2 and preceding month)

2010-3     -     34              (should be the sum of 2010-3 and all preceding months)

.

.

2012-1     -     178            (should be the sum of 2012-1 and all preceding months)

Thanks for any suggestion you may give me thanks.

~skip

[vijay_iitkgp]

Hi Skip,

I use to achieve by using Loop.

For i=year1 to year2

For J=month1 to month2

Let ym=Num($(i)&repeat(0,2-len($(j)))&$(j));

Load

$(i) as Year,

$(j) as Month

..

..

From Table.qvd Where YearMonth <=$(ym);

Here Yearmonth is concatenateion of year and Month for eg  2010 ,1 = 201001;

Hope this will help.

[Miguel_Angel_Baeyens]

Hi,

If you are drawing a pivot table or straight table chart, you can use YearMonth as dimension, Sum(NoOfOrders) as one expression and

RangeSum(Above(Sum(NoOfOrders), 0, RowNo()))

You can click on the expression in the case of the straight table and select "Full Accumulation" in the Accumulation section of the Expressions tab in the chart properties.

If you want to do the accumulation in the script, that should be something like

Table:
LOAD YearMonth,
     If(Previous(YearMonth) <> YearMonth, RangeSum(Peek('Accumulation'), NoOfOrders), 0) AS Accumulation
RESIDENT Source
ORDER BY YearMonth;

You can find more information on how to accumulate on the script in this thread and this thread, among many others.

Hope that helps.

Miguel

[#4]

Hi Vijay thanks for your suggestion.

Miguel,

This is really halpful, haven't really used the full accumulation on a table, I thoung this is only applicable in graphs...

Thanks for you help..  I will check if this can serve my all my needs.

Regards,

~skip