RangeAvg and Aggr for rolling 7-day calculation

[#1]

Hi All,

I have a chart by date which has a trend line for the moving average for the count of unitofwork for the previous 7 days. The formula below has worked out for this so far:

rangeavg(above(count(UnitOfWork), 1, 7))

Now the customer needs to be able to filter down on certain dates but since the calculation relies on the previous date's information it has to be excluded. To achieve this I used the following formula:
rangeavg(above(count({$ <Date=>}UnitOfWork), 1, 7))

Now my problem is that even when filtering down, it shows the whole line. What I really want it to do is show just that applicable segment of the line (keeping in mind that segment may rely on values excluded to calcuate correctly).

I read somewhere to try the aggr() function but this seems to mess up the original correct values that were calculated by the function above:

aggr(rangeavg(above(count({$ <Date=>}UnitOfWork), 1, 7)),Date)

I am wondering if the aggr doesn't work because it is an avg. Am I doing something wrong here?? Can anyone offer any suggestions?

Thanks in advance for your help!

J

[swuehl]

I think using the aggr() function is one possible approach.

But there is one pitfall in using the aggr() function. aggr() will sort its dimension values by load order. So if your Dates are not correctly ordered during load (chronologically), your results will be not correct.

To get around this, you can create a table with Date field with all values needed correctly sorted first in your script, then load your data, if you want, you can drop the first table then.

I remember I have posted solutions for some similar issues several times in the past, need to check.

edit: Here is a similar issue using aggr() function and the proposed solution using a sorted field first in the script.

http://community.qlik.com/message/225339