topic Re: moving average in QlikView

moving average

Frank_Hartmann — Thu, 06 Feb 2014 15:16:24 GMT

Hallo QV Community

I use the following formula in the chart for accounting the moving average:

Rangesum(above(Y,0,Row())/Row()

Is there any possibility to export the last value (=9) of the Rangesum formula to a textbox?

I tried to use the Rangesum formula within a textbox but it doesnt work for specific month.

The reason why i want to get this value to a textbox is because i need the value exported per macro to a powerpoint

document as picture or as object.

any ideas?

thanks for your help

giakoum — Thu, 06 Feb 2014 15:26:05 GMT

try to aggregate over y and take the max, something like :

Rangemax(aggr(rangesum(above(y,0,Rowno())), y))

Frank_Hartmann — Thu, 06 Feb 2014 15:49:55 GMT

thank you for your tip. the following formula works for me

max(aggr(rangesum(above(y,0,Rowno()))/RowNo(), y))

But what if the actual value is not the max value? then the value will not be shown in the textbox.

any ideas?

giakoum — Thu, 06 Feb 2014 15:52:34 GMT

add firstsortedvalue function in the expression based on x axis instead of rangemax

Frank_Hartmann — Thu, 06 Feb 2014 16:08:54 GMT

ok, now i have:

FirstSortedValue(aggr(rangesum(above(y,0,Rowno()))/RowNo(),y),x)

that gives me the firstvalue (=3), but i need the last value (=9)

what a pitty that there is no lastsortedvalue function in QV

any ideas?

giakoum — Fri, 07 Feb 2014 08:14:00 GMT

FirstSortedValue(aggr(rangesum(above(y,0,Rowno()))/RowNo(),y), -x)

Frank_Hartmann — Fri, 07 Feb 2014 09:10:14 GMT

Thank u very much