Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Not applicable

expression

can ny1 help how this function works?
=aggr( numsum(Rangesum(above(sum({1}Value),0,12))) , YearMonth)/1000000
4 Replies
Not applicable

expression

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

Not applicable

expression

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

Highlighted
adhudson
Contributor II

expression

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

Not applicable

expression

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

yojas