Aggr using unknown values

BertieBott — Sat, 16 Nov 2024 17:44:47 GMT

Hi,

I'm trying to calculate the mean moving range. The consensus across this forum suggests that an expression along the lines of Avg(total aggr(fabs(sum(Sales)- below(sum(Sales))),Date)) will do the trick.

This didn't appear to be working for me, and after a lot of head scratching it appears that it's down to my Aggr using values which I'm unsure where they've been obtained from.

Below is the table with the expressions used. It calculates the daily total correctly, the following day's total correctly, and the moving range correctly. However, the mean moving range is incorrect. Alongside the table is the values held in Aggr which I've used to try and pinpoint where the issue is. It looks like it stores the right values for the first couple of bits but when it subtracts one from the other it comes out with completely different results (There is a 9 in Aggr but not in the data table for example)

Data table

I've come to a conclusion that there's an issue with my expression which might be related to using DISTINCT?

Loading the data into another app and using the expression Avg(total aggr(fabs(Patients- below(Patients)),Date)) gives the expected outcome

Based on that I can only assume that I'm missing something from my expression.

Avg(total aggr(fabs(COUNT( DISTINCT Patient_ID )- below(COUNT(DISTINCT Patient_ID ))),ActDate))

Any ideas?

Edit: I've attached a qvw with data in. I've stripped it to only include 2 columns, and bizarrely every time I reload the app the values in the Aggr change. The COUNT values in the table do not change, the values in the fabs expression do not change, only the values held in Aggr change. They don't total the same on each reload. Should I not be using ActDate in the Aggr() (even though that is the dimension I'm using in the table?)

Thanks

Re: Aggr using unknown values

BertieBott — Mon, 07 Dec 2020 13:45:33 GMT

I managed to fix it

Although Count(DISTINCT) was always correct, Below(Count(DISTINCT) was in a different order on each reload.

I needed to order the date in the script.

topic Aggr using unknown values in QlikView

Aggr using unknown values

Re: Aggr using unknown values