Sorted vals, Set expr, AsOf-tables
Qlik
Custom aggregation with as-of table, some samples
// Sample data
LOAD *, Div(ts, 10) as tf;
LOAD *
Inline [
prog, app, ts, v
Q, c, 10, 0.5
P, a, 21, 0.3
P, b, 22, 1
Q, c, 23, 0.2
P, a, 24, 3
Q, c, 25, 2
P, b, 36, 0.1
];
// This expression gives the "last known value [v]" (sorted by timestamps [ts])
// for every entity [app], for the whole period <= selected timeframe [tf].
FirstSortedValue( {<tf={'<=$(tf)'}>} DISTINCT TOTAL <app,tf> v, -ts)
// -----------------------------------------------
// as-of table sample
tf_aotf_2:
LOAD
tf,
tf + IterNo() - 1 as aotf2
Resident main_data
While IterNo() <= 2 and tf + IterNo() - 1 <= 3 ;