topic expression in QlikView

expression

Wed, 04 Apr 2012 03:24:02 GMT

can ny1 help how this function works?

=aggr( numsum(Rangesum(above(sum({1}Value),0,12))) , YearMonth)/1000000

expression

Wed, 04 Apr 2012 05:26:49 GMT

Hi,

To understand how this expression works, we will first see how underlying functions work.

Above function will work on charts and it returns us rows above the current row in the chart's straight table equivalent.

its syntax is Above (Expression, [offset,]) So, here it evaluates expression Sum({1}Value) on the current row since offset is 0 and upto the next 12 rows above the current row.

Then this input is passes to rangesum function which will sum all the 12 rows.

Then this sum will be passes to sum function (I believe that here rather then using numsum, You should use sum) which will calculate this expression according to distinct values of YearMonth dimension then that sum will be divided by 1000000.

Qlikview will not necessarily calculate this expression in the order I have explained but it is just to explain you properly.

Ashutosh

expression

Wed, 04 Apr 2012 06:37:45 GMT

Hi ,

nice explanation by paliwal !!! ,

want to add little extra to that while using Above() function where you hard coded 12

then it means it will do Rangesum() for only 12 rows but not for 14 rows that means by mistake if you give 12 instead of 14 rows then things may go wrong so if you want to skip this situation and play safe

you can use rowno() in Above as Above ( expression,offset, rowno() ) then whatever may be no of Rows

it will automatically do rangesum for that no of rows.

Regards,

Anant Dubey

expression

adhudson — Wed, 04 Apr 2012 07:52:40 GMT

Hi,

okay. Logically speaking, the given expression will calculate the rolling 12 month summation of 'Value' if the data is sorted by YearMonth or Year and Month.

Since {1} is given inside the sum function, this will sum up all the values regardless of the currently shown values in the chart.

One more thing in this expression to note is either use rangesum or numsum function. I would suggest to use rangesum function since numsum is obsolete.

Thanks

Hudson

expression

Wed, 04 Apr 2012 08:45:37 GMT

thanx 2 all.........

yojas