topic Rolling worse Minimum average in QlikView

Rolling worse Minimum average

Anonymous — Tue, 04 Sep 2018 08:02:08 GMT

I have a tricky problem which is quite easy in Excel but I am struggling to find a solution within QlikView.

I have a list of dates and Values for which on a line by line basis I need to calculate the average of the minimum 33 values below that line. When that total changes show that diff. For my final table I only need to show the lines where there is a diff.

I have mocked up a spreadsheet (attached) which has two different methodologies. One uses an average of Small(column F) and one uses a sumIF (column E) used in conjunction with column D.

I almost get the answer using the below expression but the issue with that is once you go past the first rank <33 technically you should be then looking for the worse 34 ranks and so on.

rangeavg(Below(Avg({<Rank={"<=33"}>}[Value]),0,300))

I have explored using this formula to use the [RevisesRank] as part of the calculation or combinations of aggr but can't get either to work.

Any incite that can be provided would be appreciated.

Thanks, Ross

Re: Rolling worse Minimum average

Anonymous — Tue, 04 Sep 2018 09:45:54 GMT

I was just thinking about the problem from a SQL perspective and this is the SQL solution I would use:

SELECT A.Date, A.Value, A.Rank, A.RevisedRank, (SELECT Avg(Value) AS AvgOfValue FROM Test B WHERE B.Rank<=A.RevisedRank AND B.Date>= A.Date) As RollingAve

FROM Test AS A;

Re: Rolling worse Minimum average

Anonymous — Wed, 12 Sep 2018 15:41:12 GMT

I didn't find a more elegant solution so I went back to my SQL days...

As there will only every be 260 rows I loop through each row of the data create a temp table with the below 33 minimum value and then take an average of those 33 records. This is then aggregated into another table.