Rolling 12 months for zero values question

alexis — Tue, 23 Apr 2013 10:30:28 GMT

Hi,

I am using the following function in a chart to calculate "Rolling 12 months" for each of the ServiceZoneCodes (see example below for Service zone 201).

This function works well in a well populated data set.

= Rangeavg( Above (count({$<Year =, Month =, %PSHOrderDate =, [PSH.ServiceZoneCode]={'SE201'}>} DISTINCT [PSH.No]),0,12))

I have a scenario where a new Service Zone (see SE203 marked in GREEN below) was introduced and, unlike the other ServiceZones it does not have enough values to make up 12 values to average, so the calculated values are not representative of the true "average".

As an example the value for the first month that SE203 was introduced (Oct 2011) is 21 - here it shows "2" (see yellow highlight) because that is the average of:

(21 + 0 + 0 + 0 + 0 + 0 + 0 + 0 + 0 + 0 + 0 + 0) /12 which is rounded up to "2"

Is there a way to average the previous non-zero values only?

Your help would be highly appreciated

Alexis

Re: Rolling 12 months for zero values question

alexis — Tue, 23 Apr 2013 18:16:37 GMT

Any ideas anyone?

topic Re: Rolling 12 months for zero values question in QlikView

Rolling 12 months for zero values question

Re: Rolling 12 months for zero values question