Group (or aggregate) by n records in Qlikview?

[#1]

Hi experts,

In SQL server, I can summarize data by a fixed number of records using the query blew. I want to do the same thing in Qlikview, for example to group every 100 records (previously sorted in ascending order by date) and then aggregate results with AVG. I do not know whether it can be done, Please helps.

Thanks

WITH T AS (
  SELECT RANK() OVER (ORDER BY ID) Rank,
    P.Field1, P.Field2, P.Value1, ...
  FROM P
)
SELECT (Rank - 1) / 100 GroupID, AVG(...)
FROM T
GROUP BY ((Rank - 1) / 100)
;

[Gysbert_Wassenaar]

Maybe with the ceil function:

Result:

load GroupID, avg(FieldX)

group by GroupID;

load *, ceil(recno(),100) as GroupID

from ...presortedtable...;

[#3]

Thanks and sorry about the late response.

I would like to know whether it can be done at expression level. I want users to be able to specify the groups in the front end.

[Gysbert_Wassenaar · Accepted solution]

See here. That solution requires a macro so it may not work correctly in the ajax client.