get acumulative values in a expression

Wed, 04 Aug 2010 22:05:46 GMT

Hello there,

Anybody know how to get acumulative values in a expression but not using expression's acumulative options but from the expression, i.e: getting acumulative sales by periods. It would be something like "=Sum(SALES)" with "PERIOD" as dimension but addin or changing something else in order to get acumulative sales by period.

Thanks again for your help,

get acumulative values in a expression

johnw — Wed, 04 Aug 2010 22:35:01 GMT

If I understand your question, something like this:

// create new table with all distinct periods
// this approach is faster than using LOAD DISTINCT
AsOf:
LOAD fieldvalue('Period',iterno()) as Period
AUTOGENERATE 1
WHILE len(fieldvalue('Period',iterno()))
;
// join every Period to every AsOfPeriod
LEFT JOIN (AsOf)
LOAD Period as AsOfPeriod
RESIDENT AsOf
;
// remove future periods
INNER JOIN (AsOf)
LOAD *
RESIDENT AsOf
WHERE Period <= AsOfPeriod
;
// each AsOfPeriod is now linked to the current and previous periods

Then use AsOfPeriod as the dimension instead of Period, and a plain old sum(Sales) as your expression. All the magic to do the accumulation happens as a result of the data model.

topic get acumulative values in a expression in QlikView

get acumulative values in a expression

get acumulative values in a expression