computing offset for prev dynamically

Question

I want to set offset for prev dynamically, based on number of items in a group. for e.g

T 
| make-series value = sum(value) on timestamp from .. to .. step 5m by customer
| summarize by bin(timestamp,1h), customer
| extend prev_value = prev(value,<offset>)

The offset here should be equal to number of distinct customers. How can i compute this offset dynamically

Answer 1

If you can split query into small parts, you can use toscalar function to get number of unique customers.

---

This would be my approach...

let tab_series =
    T
    | make-series value = sum(value) on timestamp from .. to .. step 5m by customer
    ;
let no_of_distinct_customers =
    toscalar(tab_series | distinct customer | summarize count())
    ;
tab_series
| summarize by bin(timestamp, 1h), customer
| extend prev_value = prev(value, no_of_distinct_customers)

You can find example here.