topic Re: Suggestion on Scripting Backlog Transaction (Date Related) in QlikView

Suggestion on Scripting Backlog Transaction (Date Related)

Fri, 20 Jan 2012 01:52:42 GMT

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

Re: Suggestion on Scripting Backlog Transaction (Date Related)

vijay_iitkgp — Fri, 20 Jan 2012 07:29:56 GMT

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.

Re: Suggestion on Scripting Backlog Transaction (Date Related)

Miguel_Angel_Baeyens — Fri, 20 Jan 2012 07:36:40 GMT

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

Suggestion on Scripting Backlog Transaction (Date Related)

Wed, 25 Jan 2012 01:02:45 GMT

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